SSIS truncated error returned status 4

Question

I m trying to import file from Excel into an SQL table. When i convert from unicode string[DT_WSTR] to string[DT_STR] with data conversion i get a truncation error on some columns. This is output error:

[Data Conversion [2]] Error: Data conversion failed while converting column "Contact Name" (187) to column "DataContactName" (105). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

So data flow finish as failure, but some rows are copied to sql table. Is there some workaround or what is solution to this?

Answer 1

Here's what you can do

1. If you are sure that your conversion is correct and Excel does not contain any characters which are lost while conversion to non-unicode/ASCII text, thus not resulting in truncation, change your failure setting from Failure on error to Ignore on error. Though you should definitely output the error and log to a flat file for checking the data lost.
2. Check the data table's column; is it sufficient to hold every possible excel file value? If you are not sure, then try converting the table column to VARCHAR(MAX). You should not get truncation error now, if it was due to size. Other way is to simply choose a destination size for VARCHAR(N) where N=2*maximum length of the excel column.

On your comment :

I added ignore truncation errors in error output and all rows were copied in sql table. There was no problem to convert characters like ; or "á". Can u brief me what kind of errors i ignored?

I suggest that you use a Multi-cast transformation on the error output after changing the failure setting to from Ignore Failure to Redirect Row. Then insert one of the output from Multi-cast into SQL server and other on a flat file destination. This way you'll get data into SQL server as well in flat file to analyse the error