“Text was Truncated” Error in SSIS Using Excel

A colleague of mine was trying to import data from an Excel spreadsheet to SQL Server for a project and was having trouble.  She’d built an SSIS package from the import data wizard and each time it ran it threw this exception:

There was an error with output column “Comments” (42) on output “Excel Source Output” (9). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

We tried increasing the size to the Comments column in the staging table to an nvarchar(max) and using the Advanced Editor on the Excel Source to modify the output column size from the default to a few characters more than the  largest comment in the spreadsheet but nothing worked.

We finally decided to change the Comments Truncation setting in the Error Output of the Excel Source to Ignore Failure

This solved our problem and none of the data was actually truncated.

One Response to “Text was Truncated” Error in SSIS Using Excel

  1. Robert Wilson says:

    thanks for the post

Leave a Reply

Your email address will not be published. Required fields are marked *