Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert String to Datetime (USING SSIS) Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 6:20 AM
Points: 19, Visits: 172
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
Post #1457698
Posted Wednesday, May 29, 2013 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 6, Visits: 174
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.
Post #1457895
Posted Thursday, May 30, 2013 3:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 6:20 AM
Points: 19, Visits: 172
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
[url=http://connect.microsoft.com/SQLServer/feedback/details/731333/datetime2-format-not-supported-in-ssis-variables][/url]


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)
Post #1458106
Posted Wednesday, November 20, 2013 5:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:56 PM
Points: 1, Visits: 2
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.
Post #1516261
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse