Excel Source The Output Column Failed Because Truncation Occurred

In an SSIS package you have the option to choose from a wide range of source from Flat Files to writing queries using OLEDB sources such as SQL Server. Often times, such as writing queries against SQL Server, the metadata will come from the table and you won’t need to worry about truncation in the source.  Flat files are scanned and metadata is chosen based on the file so those are usually good to go as well.  Excel however tends to just use the default length on all columns of 255 characters.  This isn’t problematic until you get text that exceeds that limit at which point truncation errors occur such this:
[Admin Comments [78]] Error: There was an error with output column “Admin Comments” (92) on output “Excel Source Output” (86). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”
The size of the column needs to be increased for the data to be able to be passed into the data flow.  A data conversion transform will not be useful in this situation as the package will fail before it gets there. Take a look below for the problem and the solution.
Situation:
We have an Excel worksheet with a column called Admin Comments and in this case I have one entry in the sheet with the following text:
“This is a text box that has more characters in it than the size of the column in SSIS. The default for the column width is 255 characters. This text exceeds that limit. Therefore when the SSIS package picks up this file it will fail if you do not change the column width.”
This exceeds the 255 character default and will cause my SSIS package to fail as seen below.
I will set up my package with a data flow that is just the Excel Source and a dummy destination.
When I run the package the first time I am disappointed because of the following situation.  Errors!!!!


The error messages in that nifty screenshot above that may or may not be too small to read are the following in order of appearance.  Because SSIS can’t just give me one useful error message.  It has to give two somewhat useful and one useless in this case, it’s a feature called “Keeping you busy troubleshooting so you can stay employed”
Error Message 1:
[Admin Comments [78]] Error: There was an error with output column “Admin Comments” (92) on output “Excel Source Output” (86). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”.
Error Message 2:
[Admin Comments [78]] Error: The “output column “Admin Comments” (92)” failed because truncation occurred, and the truncation row disposition on “output column “Admin Comments” (92)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error Message 3:
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Admin Comments” (78) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Why This Happens:
Part of showing the fix for this is also showing you why this error occurred. As I mentioned my text was above the 255 characters that the default generally is set to, it has somewhere around 278 characters.  Right click on the Excel Source in the Data Flow and select Show Advanced Editor.

Under the Input and Output Properties tab you will notice a section for Excel Source Output.  Expand that then expand Output Columns.  Here a list of columns in your excel worksheet will be displayed.  Select the column in question, in this case the Admin Comments column, and notice the Data Type Properties section in the pane on the right side.

You will notice that the Length is set to the default of 255 and a Unicode string. Just above that is a property called TruncationRowDisposition and is set to RD_FailComponent.  This means that if the text exceeds the 255 characters allowed it will fail the component. Now to talk about solutions.
Solution 1 (The Recommended Solution):
There are two solutions for the particular situation that we will discuss.  The first is to simply change the Length in the Advance Properties to a value that allows all the text in the field to be brought back.  Since my widest column is 278 characters I will set mine to 300 because I know that is the maximum in my source system so the field can’t have more data than that. Simply change the value to 300 as shown below.

Now when you refresh the metadata for the other components in the package and run it everything works wonderfully.

Easy fix! Just change the column length in Advanced Editor for the Excel Source and you are good to go!
Solution 2 (Not so recommended):
The other option is to change the TruncationRowDisposition property from RD_FailComponent to RD_IgnoreFailure.  In this case if the row exceeds the length setting the data will still be brought in, but only up to the length. So if the setting is at 255 and there are 300 characters only the first 255 are passed in. This is not recommended for obvious reasons since you loose data, but sometime that is acceptable.

About the author

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

19 comments

Leave a Reply to dinesh Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Thanks Bradley. I think Microsoft really missed the entire ocean on SSIS when it comes to ease of use. I’ve been frustrated by many of these similar situations while importing Excel. Maybe someday Microsoft can build a “wizard” again that steps you through the process, showing you when the imports are going to fail, then giving you actual clickable options on how to fix them without having their users spending time on Google to find how someone else fixed the same issue. Maybe Microsoft just like to support 3rd party tool makers?

  • I have a proposed Solution #3.
    In Excel 2007, you can break up the column that exceeds 255 into 2 or more columns depending on the number of characters in the column. You won’t lose data and can concatenate in SQL once it’s been imported, if you need to. In Excel, insert 1 or more columns to the right of the large column that exceeds 255 char. Then, select the large column, choose the Data menu, choose Text to Columns on the ribbon. A wizard pops up to choose delimited or fixed width. Choose delimited or fixed width, choose Next, in Step 2, if you chose fixed, scroll down in the data preview to one of the larger width entries, the ruler will extend automatically. You want to set the break line at 250 or 255, chose Next, step 3 lets you format the text, then Finish.

  • When using Package A with Data File A on Machine A to Source A, I receive no errors. Then I attempt the Package A with Data File A on Machine B to Source A and I receive this error. If I fix the error on Machine B using your Solution 1, I now receive a “violation of primary key” error. However, again, it’s puzzling that Package A with Data File A run smoothly on Machine A to Source A. Any advice?

    • I tried the solution 1…but still throwing an error….i tried the another way changing typeguessrows value to 0 instead of 8 …still it is not working… can someone help on this?

  • I tried the 1st solution, used the max 4000 length, but it still reads only 255 characters from the column and got that truncation error message.. any idea?

  • My SSIS package does not seem to remember the length I set for my fields sometimes, and resets them back to 255 when I open it again. Any ideas ?

  • I have issues with trying to import Excel files with columns that have LONG text, like 2-8k characters. Any help? Changing it to dt_text doesn’t work. Maybe I’m doing the wrong steps.

  • This solution (solution 1), does not work. I set the output column to 2000 (max character I have is 290), but still I have same error. I believe external column length (which defaults to 255) even changed is issue. I do not want to create dummy rows and don not have rights to change regedit property.
    Could you please let me know if I am missing something.

    • I would try to take just one row that you know is over under the 255 limit and feed it through to see if the problem persists. Then you will know the issue is maybe something that you aren’t currently looking at. Then take a row that is over the 255 limit and see if it fails and you can narrow it down a little. Unfortunately the best way to troubleshoot is to take a subset of the data to narrow things down.

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me