question

tombiernacki avatar image
tombiernacki 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.
ssisdatetimedate
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You need to use Data Conversion Transformation in SSIS to change the datatype from MySQL to what is expected in SQL
0 Likes 0 ·
tombiernacki avatar image
tombiernacki answered
I was able to fix the issue by changing the datatype from date to nvarchar and that worked for me
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I was able to fix the issue by changing the datatype from date to nvarchar and that worked for me
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.