Search code examples
sql-serverdatetimessisetlderived-column

SSIS Date/Numeric Conversion from Flatfile

I have a Flat file which contains 2 columns . One is date time in this format

2017-11-06T11:16:08 AM GMT

and Another Column Total value stored in this format

£39.00.

Destination is OLEDB (ms sql), 

Column 1 is Datetime and column 2 is Int in the destination

Data conversion in SSIS using Derived column is not returning required output for Column 1 (date)

PS: Destination data type can not be altered

enter image description here

FlatFileSample Data

Derived Column:

Column 2 is returning values with substring 

(DT_STR,50,1252)SUBSTRING(Total,3,20)

Column 1 (orderDate) need to replace T and Am/Pm Values , output should be 

2017-11-06 11:17:40   from 2017-11-06T11:16:08 AM GMT

enter image description here

Solution

  • You can use the following expression to achieve this:

    SUBSTRING([Order date],1,10) + " " 
+ (FINDSTRING([Order date],"AM", 1 ) > 0 ? SUBSTRING([Order date],12,2)  : SUBSTRING([Order date],12,2)  == "12" ? "00" : (DT_WSTR, 2 )((DT_I4)SUBSTRING([Order date],12,2) + 12))
+ SUBSTRING([Order date],14,6)