Data Truncation error in SSIS while Reading Excel Source

In the SSIS Package you can choose from multiple sources like OLEDB, Flat files, XML source or Excel source. Reading data from OLEDB source is usually OK as metadata will come from the table. This is not the case with Excel source as it analyze 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 I will explain three ways which we have used to fix this error and got my 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 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:

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:

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

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.