Convert String to Datetime (USING SSIS)

Question

I want to insert the value "5/27/2013 16: 42: 37.490000" (read from a flat file (DT_STR)) into the (datetime) column of the SQL Server table. If I try to pass it (DT_DBDATE) or DT_DBTIMESTAMP in a derived column, this will result in 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 do this?

thanks

Answer 1

The value is a datetime2 type. SSISAFAIK does not support datetime2. You need to save it in the database as a row, and then update the column by translating it to datetime2.

Here is the problem with Microsoft Connect

Update. Using DT_DBTIMESTAMP2 , you can convert the string to the appropriate date and time format

Below code works fine in Derived Column

 (DT_DBTIMESTAMP2,7)"2013-5-27 16:42:37.490000" 

7 is the precession here. The above code will not work if the datetime format is different. For example, MM/DD/YYYY HH:mm:ss.ffffff .

However, you can use the Script component and pass an array of different datetime formats to the Datetime.ParseExact function

Step1: Drag the Script component and create a new DT_DBTIMESTAMP datatype output column and name it NewDate .

Step 2: write below C # code

 public override void Input0_ProcessInputRow(Input0Buffer Row) { string[] format = new string[] { @"M/dd/yyyy HH:mm:ss.ffffff", @"MM/dd/yyyy HH:mm:ss", @"M/d/yyyy HH:mm:ss" , @"M/dd/yyyy HH:mm:ss.ffffff", @"MM/dd/yyyy HH:mm:ss.ffffff", @"M/d/yyyy HH:mm:ss.ffffff"}; DateTime dt = DateTime.ParseExact(Row.Date, format , CultureInfo.InvariantCulture, DateTimeStyles.None); Row.newDate = dt; } 

Answer 2

I will not say that this is the perfect solution, but just a workaround. Format the input and paste. Formatted data after using the expression below 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)