Failed Because Truncation Occurred

  • Dear Group:

    I am getting the following error on my SSIS import process.

    "Workbook.Outputs[Excel Source Output].Columns[XYZ]" failed because truncation occurred, and the truncation row disposition on "Workbook.Outputs[Excel Source Output].Columns[XYZ]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component."

    I understand the issue relates to the column XYZ being too large, and it being truncated. But for the life of me, I cannot figure out the solution.

    In my database, this column is set to NVARCHAR(512). The column in question only has a max value of 265, but something in my SSIS package isn't updating to the column size.

    In my Data Source step, I did the following:

    - Right Mouse Click and go into Advanced Editor

    - Went to Input and Output Properties

    - Checked column in question in "Output Columns" and the Length is set to 512 Unicode string [DT_WSTR]

    - However, I noticed in the "External Columns" portion, the Length is only 255. However, I cannot seem to update this value, as every time I change it to 512, it reverts back to 255

    As such, I am not sure if the "External Column" in the Advanced Editor is the issue and if it is, how to update the value and keep it from reverting back to the original 255 value.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Figured this out for you:

    https://data-dev.blogspot.com/2017/03/ssis-export-data-to-excel-with-more.html

    It is a limitation of the SSIS Export to Excel that 255 characters is the max.  The workaround was to export to a flat file (txt, csv, etc) and the convert the flat file to Excel.

  • Mr. Gail:

    I appreciate the response and not trying to be rude, but unfortunately I am not sure this helps.  I am not trying to export to an Excel file, but import data from an Excel file when I am getting this error.

  • Ok, I just misread the error.

    If you import into memory (ie have no output step on the Excel import), do you still get the same error?

    I am just thinking MAYBE the error is  happening at a slightly different place than the error message.

    Alternately, what if you close out of visual studio, rename the .database file(s) and .user file(s) and then re-open the project?  This should cause visual studio to re-grab the source metadata and possibly fix the 255 character thing.

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply