Convert String to Datetime (USING SSIS)

Question

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 go about it ?

Thanks

Answer 1

The value is a datetime2 type .AFAIK 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

Update: Using DT_DBTIMESTAMP2 you can convert the string to proper datetime format

The below code works perfectly fine in Derived Column

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

7 is the precession here .The above code won't work if the format of datetime is different.For example MM/DD/YYYY HH:mm:ss.ffffff .

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

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

Step2: Write the 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 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)