SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

Using SQL Server 2005 SSIS to import an Excel Spreadsheet into a SQL Server database,  I got the error shown in the title.

The problem was that the Excel spreadsheet column ‘Comments’ contained data which was bigger than the maximum size of the Excel Source External Column ‘Comments’ as defined in the Excel Data Source.

The data types of the Excel Source External Columns are automatically determined by SSIS based on sampling of the spreadsheet rows. For string columns if the sample does not reveal cells with greater than 255 characters, the data type is always DT_WSTR (Unicode string) which has maximum length 255. If the sample reveals cells with greater than 255 chars then the datatype will be DT_NTEXT (Unicode text stream).

I was unaware of how the Excel Source datatypes were derived and since the SSIS sample came up with only short strings in ‘Comments’, it assigned it a datatype of DT_WSTR. My full spreadsheet data for ‘Comments’, however, contained cells with strings of greater than this length, hence the truncation error when the SSIS package was executed.

The reason that the datatype for an Excel spreadsheet string column are always DT_WSTR or DT_NTEXT is given by this Microsoft Technet article, ‘Excel Source’ which explains that the datatypes are determined by the Jet Database Engine used by the SSIS Excel Connection Manager to communicate with Excel.

It is impossible to edit the datatype of an Excel Data Source column in SSIS therefore it is impossible to avoid this truncation error unless you edit your spreadsheet data, convert the Excel spreadsheet to a flat file, or increase the sample size as described in the Technet article in the link above.

I decided to convert the Excel spreadsheet to a flat file.

Convert Excel Spreadsheet To A Pipe-Delimited File

This is where my forty minutes turned into three hours.

Since I was not aware of how to increase SSIS sample size at the time, I tried to convert the Excel spreadsheet into a flat-file. Stupidly, I first converted it into a comma-delimited (.csv) file. Of course I ran into problems with phantom columns and incorrectly parsed data because my spreadsheet has embedded commas in its data.

So I tried a Find and Replace in Excel to convert commas to spaces and ran into the Excel ‘Formula Too Big’ error for cell data greater than 900 characters. Since Find and Replace could not work, I located a VBA macro written by aht-ga which did it. This Macro works, but took three hours to run over 1MB of data (cell selection was 1230 rows x 25 columns).

Gibbering with horror at the prospect of ever having to run this Macro again, an epiphany struck me in the form of rememberance of pipe-delimited files. Excel can be forced to make pipe-delimited files by this trick posted by Martin Naranjo.

I now possesed a beautiful pipe-delimited file which I included in my SSIS package using a Flat File Data Source. Flat File Data Sources, unlike Excel Data Sources, can have the datatype of their External Columns edited.

Editing The DataType Of SSIS Data Source Columns

Bring up the SSIS Connection Manager Editor by double clicking on the Connection Manager
for your Data Source in the package’s Connection Managers tab. When you do this for an Excel DataSource you will see a landscape as barren as the Gobi Desert Municipal Swimming Centre, but if you do this for the far more flexible Flat File Data Source a plethora of options will greet you. Click on ‘Advanced’ and slash away to your heart’s content.

Moral Of The Story
When using an Excel Data Source, increase your SSIS sample size to a humungous number so that it can assign the correct datatype to the Excel Data Source External Columns.

Problem Metrics:

    Life Energy Consumed: 1 Hr. (not including Macro running time)

Caveats, Apologies and Clarifications:

Aht-ga’s VBA Macro is excellent. It just took too long to run in my scenario.

Advertisements

Tags: , ,

16 Responses to “SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.””

  1. Alley Says:

    Thanks for this article. After spending a few hours trying to debug my package, found this article on the net and took me less than a minute to correct my package.

    Thx again

    ak

    • baraholka1 Says:

      Alley,

      Very glad to be of assistance. This particular article gets loads of hits Its probably the best article on the site and, as it happens, the first one I wrote!

      – Barra

  2. “Text was truncated or one or more characters had no match in the target code page.” « ruud·i·men·ta·ry Says:

    […] issue by copying the data from one Excel sheet to a new one and all other kind of tricks I found this blog post about this on the Bite The Wax Tadpole […]

  3. Importing Excel XLS to SQL SSIS error | Uncategorized on Dan Israel Says:

    […] were a number of posts inlcuding David Klein’s and Waxtadpoles regarding SQL 2005 and the “TypeGuessRows” setting in the registry.  Thier suggestion […]

    • baraholka1 Says:

      Dan,

      Excellent additional information on resolving this error in SQL Server 2008.
      Thanks for dropping by.

      – Barra

  4. Craig Says:

    This was a great article. Because this article told me that the problem was a result of reading the length of my excel fields, i was able to come up with a very simple solution.

    If you get this error just open up your excel document and find the row that has the longest text for the column giving you an error. Then simply cut that row and paste it at the top of your document. Now when you load your excel datasource in SSIS it will calculate the column width correctly and load fine.

  5. Shane Says:

    I don’t understand how this is helpful. If I was in control of the file and it’s contents, I most likely wouldn’t be using SSIS to import the data. So, the short answer is: Microsoft’s products are partially incompatible with their other products rendering them often times useless. Nice one MS.

  6. Marcel Says:

    Thanx a lot my god!

    I simply changed the registry setting from 8 row sample to 300 rows!

    Which stupid bastard of hell has choosen here 8 rows as a sample for deciding the data type??? Has this ever worked in real projects of a real company? Excel data are always dirty.

    • baraholka1 Says:

      Marcel,

      Very glad to be of assistance.
      Since I do not wish to be struck down dead by an Angel Of God and immediately have my corpse eaten by ravenous worms in full public view (unlike naughty King Herod – see Acts 12:23)however, I must resile from any inference that I am divine.

      In future you may write “Thanx a lot my waxy ubercodemaster” or somesuch.
      Better yet just send money.

      All The Best,

      Barra

  7. BS Says:

    I have job which import excel data to database, sometimes column has less then 255 character or more then 255. I set column width DT_NTEXT, whenever column have less 255, it fails. Any solutions to this issue.

    Thanks in advance.

    BS

  8. VP Says:

    Getting same error as BS

  9. Jayashree Says:

    This helped me a lot to solve the issue.Thanks

    • baraholka1 Says:

      Hi Jaya,

      Yes, this one is a BTWT classic. Published 5 years ago and still rescuing developers from despair.
      It gets the most hits of any of my posts.

      All The Best,

      Barra

      • dave Says:

        Every February for the last five years I have struggled with importing a spreadsheet used for our youth lacrosse league tryouts. FIVE YEARS. You can imagine how hard I laughed when I read that you published this 5 years ago! The website our lax club uses handles data like a preschooler in a watch factory so I never googled to find a solution. I just figured there was something screwy with the data and left those fields off the sql import and re-attached when I exported spreadsheets for our coaches to use. Five Years! What a riot!

      • baraholka1 Says:

        Dave,

        Three words: Journey Of Pain.
        Thanks for the best BTWT blog comment ever.
        You have just made my whole programming career worthwhile.
        Most glad to be of assistance.

        Regards,

        Barra.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: