Common SSIS ETL issues

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:

image27

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 detected all possible sources of this error: Coming from Derived Column task and/or Destination task, where the destination column is of a smaller width than the source/derived column:
image28

We solve this by adding more space to the field: If this error is in the derived column, add (DT_STR,#new width, 1252):

image29

– “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:

image25

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

image30

“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

Increase Timeout

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

Arithmetic overflow

Solution: If you are using SQL Server Destination, consider replacing it with a OLE DB destination.

Leave a Reply

Your email address will not be published. Required fields are marked *