Tuesday, January 12, 2010

SSIS - Annoying Truncation Error

Several of my SSIS projects involve importing data from CSV files. Most of the time if the CSV file contains any text the import will result in the following error in SSIS: "Text was truncated or one or more characters had no match in the target code." I have used a few workarounds to avoid this message. Most of the solutions were posted in a thread on Technet. I will post here the couple of workarounds that has worked for me.

Workaround #1
The Jet engine determines the column types and lengths by the first 8 rows. If after the first 8 rows there are rows that contain text data longer than what is in the first 8 rows, you will get that error. So you can put in a fake row in the very first row with long strings of text. Or move one of the existing real data row with long strings to the first row. It's a bit clumsy but works.

Workaround #2
Convert the CSV file to an Excel workbook. Works like charm!

One other solution has included messing with the registry to tell the JET engine how many rows it should base its guess on the data length. Check out the previous page I posted on the details. I have not tried that but I may have to do it in the future.

No comments:

Post a Comment