Returning SSIS status with truncated error 4
I am trying to import a file from Excel
to SQL table
. When I convert from unicode string[DT_WSTR]
to string[DT_STR]
data conversion, I get truncation error on some columns. This is the output error:
[Data Conversion [2]] Error: Data conversion failed when converting column "Contact Name" (187) to column "DataContactName" (105). The transformation returned a status value of 4 and the status text "The text was truncated or one or more characters did not match on the target code page."
So the data stream ends up crashing, but some lines are copied to SQL table
. Is there some kind of solution or what is the solution?
source to share
Here's What You Can Do
- If you are sure that your conversion is correct and Excel does not contain any characters that were lost when converting to non-unicode / ASCII text, thus without causing truncation, change the fallback setting from
Failure on error
toIgnore on error
, although you should definitely output an error and log into a flat file to check for lost data. - Check the column of the data table; is it enough to keep every possible value of the excel file? If you are unsure, try converting the table column to VARCHAR (MAX). Now you shouldn't get a truncation error if it's size related. Another way is to just select the destination size for VARCHAR (N) where N = 2 * the maximum excel column length.
To your comment:
I added ignore truncation errors in the error output and all rows were copied to the sql table. There was no problem converting such characters; or "á". Can you tell me what mistakes I was ignoring?
I suggest you use the Multi-cast conversion on the error output after changing the fail parameter from Ignore Failure
to Redirect Row
. Then paste one of the results from Multi-cast to SQL Server and others to flat destination file. This way you will get the data on the SQL server as well as in a flat file to analyze the error.
source to share
It looks like the length specified for the DataContactName when transforming the data is less than the length of the "Contact Name". Check the maximum length of the Contact Name column and set the size to DataContactName. You see multiple rows inserted because they may have been inserted before receiving longer data.
source to share