May 13, 2021 at 2:36 pm
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.
May 14, 2021 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 14, 2021 at 3:31 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 14, 2021 at 3:34 pm
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.
May 14, 2021 at 3:59 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply