Data Truncation error in SSIS while Reading Excel Source

Data truncation error in SSIS is the most common error if you are reading data from some flat file or Excel source. In this post we will look at how to fix truncation error while reading an Excel file. Excel source analyzes first 8 rows (row 2 to row 9) to set the data type for all of the columns and their lengths. If these rows contain string data with length below 255 characters, it will set the length of that column to 255 characters and data type will be Unicode String. If there is a row, after the row 9, and data for any of the string column length exceeds 255 characters then package will fail and generates a truncation error:

Text was truncated or one or more characters had no match in the target code page.

Now you need to somehow increase the length of that column according to the row on which Data Flow task got failed or set the data type to ntext instead of Unicode String. You can increase the column length using Data transformation component but in this situation it will not be helpful as Data flow task will fail before reaching there. There are many ways to solve this issue, but here we will explain three ways which we use to fix this data truncation error and get our SSIS package working.

Solution 1:

As mentioned above, Excel source analyze first 8 rows to set the data type and length of every column. So if your package is failing due to a truncation error on a string column, simple way is to add a dummy row as first row in the Excel file with data greater than 255 characters in the column that is causing truncation error. Using this simple technique Excel source will change the data type of that column to ntext and truncation error will be solved.

Solution 2:

Other solution to fix the data truncation error is by simply increasing the length of that column or changing the data type to ntext. You can do this by right clicking on Excel source and it will open the “Advanced Editor for Excel Source” window. Next, you need to go to the “Input and Output Properties” tab and expand “Excel Source Output”. Under “Excel source output” expand “Output Columns” and you can see all columns in that excel file. Here you can click on the specific column whose length you want to increase or change the datatype using “Data Type Properties” as shown below:

data truncation error in SSIS

 

Solution 3 (Not Recommended):

There is another solution to fix this issue, however it is not recommended. Excel source uses first 8 rows by default to set column data type and length but this can is controlled by a setting in the windows registry. You can go to the windows registry and change “TypeGuessRows” property value according to your need. To change this property Press Windows+R and type “regedit” and press the OK button:

Excel truncation-2

It will open the Registry Editor. Now by using CTRL+F you can search property “TypeGuessRows” in the windows registry:

Excel truncation-3

 

This property allow values from 0-16. If you want the Excel source to analyze the whole file in order to set column data type and length, you can set it to 0. Please note that this solution is not recommended as it will slow the package because Excel source will take more time to analyze columns data type and length.

Hopefully by following the solutions described in this blog post, you can fix the truncation error while reading an Excel source and your package will executed successfully.

 

 



Leave a Reply