Convert String to Datetime (USING SSIS)

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;
 }