Convert String to Datetime (USING SSIS)

Question

Convert String to Datetime (USING SSIS)

connect.akshay

Say Hey Kid

Points: 667
More actions
May 29, 2013 at 6:41 am

#148165

HI Guys. Need some help.
I want to insert a value "5/27/2013 16:42:37.490000" (Read from a flat file (DT_STR)) into a column(datetime) of SQL Server table . If I try to cast it with (DT_DBDATE) or DT_DBTIMESTAMP in a derived column , it gives an error
[Derived Column [130]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (130)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (155)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
How can I correct this ?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

perwi SSC Veteran Points: 222 More actions · Answer 1

I'm not sure here but I think the string is too long. Maybe not the best solution but try to remove a couple of zeros at the end.

convert(datetime,substring('5/27/2013 16:42:37.490000',0,23))

In ssis there is also a transformation named Data Conversion that you could try instead of a Derived Column.

connect.akshay Say Hey Kid Points: 667 More actions · Answer 2

The value is a datetime2 type . SSIS doesn't support datetime2 .You need to store it in database as string and then update the column by converting it to datetime2.

Here is Microsoft Connect Issue

I won't say it's the perfect solution, but just a workaround. Format the input and then insert. The formatted data after using the below expression works well with either datetime or datetime2.

SUBSTRING(TMS_CREAT,(FINDSTRING(TMS_CREAT,"/",2) + 1),4) + "-" + SUBSTRING(TMS_CREAT,1,(FINDSTRING(TMS_CREAT,"/",1) - 1)) + "-" + SUBSTRING(TMS_CREAT,(FINDSTRING(TMS_CREAT,"/",1) + 1),2) + SUBSTRING(TMS_CREAT,(FINDSTRING(TMS_CREAT,"/",2) + 5),16)

grace.nash SSC Enthusiast Points: 158 More actions · Answer 3

I was able to do this via SSIS by setting the data type on the destination table to datetime2(7) then letting SSIS assign the default string(50) datatype to the field. You'll get a truncation warning when setting up the mappings/conversions, but if you set to ignore they will still import. You don't get a warning-free package completion result, but the data will import. One other note. When setting up the package via the wizard, it would not let me execute the package upon wizard completion (could only save it). But I executed immediately after - again with a few nasty warnings - but the import completed.