2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ven
  • 2,011
  • 1
  • 13
  • 27
  • Will column 2 always have data in whole pounds only? – Mazhar Nov 06 '17 at 14:51
  • Yes Always have data in those formats, but the destination does not have any symbols – Ven Nov 06 '17 at 14:52
  • You can do the data conversion in the `OLE DB Destination` with `CAST` or `CONVERT` – Mazhar Nov 06 '17 at 14:58
  • Ofcourse i knew, but destination structure can not be changed. – Ven Nov 06 '17 at 15:16
  • Post the code from the derived column and ole db destination in the question? – Mazhar Nov 06 '17 at 15:18
  • 2
    Personally I NEVER load directly to the production location. I always load to a staging table (in this case with varchar/nvarchar fields), do the clean up and then load to production. You will save yourself a lot of problems that way. If the conversion fails for instances, Then you have the data available to see what went wrong, rather than trying to see what is in a million record file. – HLGEM Nov 06 '17 at 16:20
  • I can set up alerts or notifications for failure for packages, i wouldn't even like to load this directly to the mapping tables, it will be straight froward if i use staging tables. Unfortunately requirement states the above. – Ven Nov 06 '17 at 16:23

1 Answers1

1

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)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Apologies for late reply, i think FINDSTRING works only with the DT_WSTR data type. It is DT_STR. I believe there must be a workaround with this – Ven Nov 07 '17 at 11:16
  • @BHouse `FINDSTRING` works for both string types (DT_STR & DT_WSTR), also you can simply cast the `Order Date` column using `(DT_WSTR,50)[Order date]` – Hadi Nov 07 '17 at 12:04
  • Solved Thank you – Ven Nov 07 '17 at 14:03
  • @BHouse if this answer solved your question you should [accept it](https://stackoverflow.com/tour). just click on the mark under the voting arrows – Yahfoufi Nov 07 '17 at 14:35