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.
asked Oct 17, 2012 at 04:14 PM in Default
I was able to fix the issue by changing the datatype from date to nvarchar and that worked for me
answered Oct 17, 2012 at 09:35 PM
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. 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.
answered Oct 17, 2012 at 08:17 PM