SSIS to SQL DateTime data type conversion failing

I am a beginner with MS SQL server 2014. Need some advise with data type conversion from SSIS to SQL server.

I am working with DATE TIME field, an example value : '01272016122044123' which is in format 'MMDDYYYYHHMMSSsss'

From SSIS I have used data type "database timestamp with precision [DT_DBTIMESTAMP2]" and in SQL Server I have used data type "DateTime2(7)".

When I am trying to import the data from SSIS it gives me error "An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format".

The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Need to know where exactly i go wrong ?

Thanks Syed Ayaz Mahmud

1 Answer

SSIS is super fussy about converting a string to datetime. I would do a script task in a dataflow instead:

1. Select the value from the database as a string
2. Add a script component transformation
3. Create a column in the output buffer that is "database timestamp with precision [DT_DBTIMESTAMP2]," making sure that you adjust the scale to 3

4. Use the following in the script:

   using System.Globalization;
   public override void Input0_ProcessInputRow(Input0Buffer Row)
   { CultureInfo provider = CultureInfo.InvariantCulture; Row.newDate = DateTime.ParseExact(Row.MyDate, "MMddyyyyhhmmssfff", provider);
   }