Message: Cannot convert between unicode and non-unicode string data types:
This happens when we want to insert unicode (such as MySQL varchar with latin_swedish collation) data into a non-unicode (such as . Or a non-unicode (such as MySQL varchar with utf8 collation) into a unicode field (such as SQL Server Nvarchar or Nchar). The problem is common on the net:
I solved it by Derived Column task and add a new field in this task. And depending on the two fields:
1. When inserting from unicode column to non-unicode column (varchar): Prefix your field in varchar with this string conversion: (DT_STR,80,1252).
2. When inserting from non-unicode (varchar utf-8) column to unicode column (Nvarchar): Then prefix your field in varchar with this string conversion: (DT_WSTR,80,1252).
Message:“The component derived column failed because truncation occurred and the truncation row disposition”: I detected problems in string lengths so I changed the widths in derived column task.
We solve this by adding more space to the field: If this error is in the derived column, add (DT_STR,#new width, 1252):
– “the value violated the integrity constraints of the column”: I detected an error in transferring datetime from MySQL into SQL Server > narrowed it down > Found 0000-00-00 values allowed in MySQL, whereas SQL Server refuses them:
We solved it by replacing the field in the query by:
IF(export_date<>’0000-00-00 00:00:00′,export_date,STR_TO_DATE(‘1900-01-01 00:00:00′,GET_FORMAT(DATETIME,’ISO’))) AS export_date11
“Arithmetic operation resulted in an overflow.” Reason: Processing Bigint and TinyInt in ADO.NET sources – ADO.NET doesn’t recognize Bigint and Tinyint. Solution: Convert Bigint and Tinyint into char or varchar, then optionally convert them again in a derived column task into a bigint or tinyint.
Bulk Insert Operation of SQL Server Destination has timed out
Solution: Go to the SQL Server Destination task in your package > Properties > Time out > Increase the value (default is 30-60).
Run this package as an administrator
” Unable to bulk copy data. You may need to run this package as an administrator.”
More information about his at MS site.
Solution: Run the package program (SSMS, Agent, etc.) as an administrator. Right click on SSMS icon on windows then choose > Run as administrator.
Restart the SQL Server service and re-run the SSMS (Management Studio) as an administrator.
Arithmetic Operation Resulted in an Overflow
Solution: If you are using SQL Server Destination, consider replacing it with a OLE DB destination.