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