2

I have a data factory in Azure that has a pipeline to execute an SSIS package. An extract script is included in this package that will convert a date value from 2019-01-13 00:00:00 to 2019-01-13. After this, a logic app picks up the extract.txt file and sends it to sftp to drop into a folder.

As you can see from my extract script in SSMS converting a varchar data type to date (I receive date in Unix), for Arrival date the following value is given.

Script

Script

Data viewer

Date Value

However when I open my extract.txt file, the date shows as: 2019-01-13 00:00:00.0000000

Can anyone tell me why they think this may be happening? Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
Watsyyy
  • 125
  • 10

1 Answers1

1

When you map a date column to flat file it will include time even if it doesn't appear in data viewer or ssms.

SSIS solution

Instead of using dateadd() in the source, leave the column as it is, in SSIS use a derived column with the following expression:

LEFT((DT_WSTR,50)DATEADD("s", [start], (DT_DATE)"1970-01-01",10)

Output

2019-01-13

Based on the Cast (SSIS Expression) official documentation:

When a string is cast to a DT_DATE, or vice versa, the locale of the transformation is used. However, the date is in the ISO format of YYYY-MM-DD, regardless of whether the locale preference uses the ISO format.

SQL Server solution

Use the following SQL command:

select convert(varchar(10), dateadd(S, [start], '1970-01-01'),120)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi, Sorry for only getting back to this now, but I have been out of office. This worked correctly, I tried the SQL Server Solution and it gave me the format of the date I was looking for. Thanks very much! – Watsyyy Mar 06 '19 at 10:26