asked
SSIS package and SQLDateTime Overflow
Hi I am exporting table from mysql database into a sql database. I created an SSIS dataflow task. My issue is when I execute the package I get the SQLDateTime Overflow error message. The message is cause by one column called Inactivated. If I exclude the Inactivated column, all date is exported into the sql table. In mysql the Inactivated column datatype is date and when I created the table within sql, I tried date, datetime, datetime2 but to no avail. In mysql I also tried changing the date dateype to datetime but had the same error when executing the ssis package. Any Ideas would be appreciated.
I was able to fix the issue by changing the datatype from date to nvarchar and that worked for me
First check in the Advanced editor the data type of the source columns. In a lot of cases the SSIS choose a DT_STR (string) and let the driver to convert the date/time values to string. Then it tries to convert it back to date/time SQL format. When SSIS is converting string to Date/Time types, it expect the date/time string is in particular format. If the source string is not in right format, the conversion fails. You can check needed format on MSDN: [Integration Services Data Types][1]. There is a section **Converting Between Strings and Date/Time Data Types**. You can check what you receive from the source by redirecting the error records to error output, connect the error output to a row count for example and add a data viewer to that Data Flow Path to inspect the values. The best way to avoid the date/time conversion problems is to convert the date/time values in the query to the source system to the string, which match the format requested for conversion by SSIS. Than you can use the **`Data Conversion`** transformation to convert the properly formatted string to Date/Time. [1]:
http://msdn.microsoft.com/en-us/library/ms141036(v=sql.105).aspx
I was able to fix the issue by changing the datatype from date to nvarchar and that worked for me
10 People are following this question.
