Brief background of the system:
Using an SSIS package to move data from SQL Server to MySQL.
I use an OLE DB connection to connect to SQL Server. I use an ODBC Unicode connection to connect to MySQL.
Most of the system works, but I am having a date conversion issue when passing an SSIS Variable into an Execute SQL Task. It attempts insert a date into a table on the MySQL database. I get the following error when trying to enter a date of {1/1/2010 12:00:00 AM}.
"[Execute SQL Task] Error: Executing the query "Insert into ETLDate values(?)" failed with the following error: "[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.22-log]Incorrect datetime value: '-10165-58296-9719 16613:13824:2136' for column 'LastETLDate' at row 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The date is consistently '-10165-58296-9719 16613:13824:2136' so I know the value is getting passed correctly, it's just converted or encoded differently. Here is my setup for the parameter.
Some final notes:
I've tried all the other date data types for the parameter and I get the same issue.
I've tried with just a direct insert statement using the parameter and not a stored procedure and I get the same issue.
All data flow tasks that move data (which includes dates) from the SQL Server DB to the MySQL database work fine.
If create a similar stored procedure and table on the SQL Server DB (with updated parameter settings for the OLE DB connection) it works fine.
Any one know why the date is getting passed incorrectly or have a work around for this issue?
