SSIS to SQL DateTime data type conversion failing

Question

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

The way that the date is formated right now I think SQL thinks it is an INteger and there isn't a date format that fits that number. If you put it into excel and select a date format I am gonna guess it won't work either. Check out this website it will help msdn.microsoft.com/en-us/library/ms186724.aspx. You may need to get a little creative with SQL to format the number the right way. — Wes Palmer
Hi Palmer, you are right, even when i put that to excel sheet and format it as date/time (tried all sorts) none works. Later on, I have tried to take it as INT and then import to SQL (it works fine). The problem is now I have to use SUBSTRING to re-decorate the numbers. Let me know if you have any other alternatives in your mind. Thanks. — Ayaz
If you can use it SUBSTRING after insert, just use it before.... and insert correct date time format: ` YYYY-MM-DD hh:mm:ss[.nnn]`. — Justin

Mark Wojciechowicz Mark Wojciechowicz · Accepted Answer · 2016-01-27T18:38:55

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

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

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

SSIS to SQL DateTime data type conversion failing

1 Answers