Excel Source The Output Column Failed Because Truncation Occurred

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

19 Responses

  1. WiseOldMan says:

    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?

  2. Kathy says:

    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.

  3. dinesh says:

    I don’t think this will work.

  4. dinesh says:

    I followed your instructions. I still get the same error.

  5. Riona says:

    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?

  6. Ross says:

    you’re an angel!

  7. neeth says:

    I tried solution 1…. but its still throwing an error … can some help on this

    • Dhinesh Siva says:

      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?

  8. Bishal Agrawal says:

    I tried the solution 1 ,its working fine.
    Thank you

  9. Peter Kiss says:

    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?

  10. Perry says:

    Thank you!

  11. jude says:

    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 ?

  12. Wow, I wish I had read your column about 3 hours ago….I’ve spent that much time trying to figure this out.

    Thank you!

  13. j says:

    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.

  14. arbind says:

    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.

  1. January 12, 2014

    […] the source to ignore the failure. The last option does give you truncated data though. This blog post by  Bradley Schacht (blog | twitter) describes how you can make those […]

Leave a Reply