Exporting data to excel via SSIS, column width issue

Question

Exporting data to excel via SSIS, column width issue

Adam Bean

One Orange Chip

Points: 26631
More actions
August 10, 2010 at 12:43 pm

#224497

Hey all,
I am trying to export data into an excel spreadsheet via SSIS and getting the following message: "Error 0xc0204016: SSIS.Pipeline: The "output column "Description" (179)" has a length that is not valid. The length must be between 0 and 4000.". The source column is a varchar(6000) and the destination is LongText.
I don't understand why it won't support larger than a 4,000 character column as I'm able to setup a data connection from within excel to query the database directly and the data is not truncated. I would prefer to setup an extract vs. allowing users to update the data at their desire, but I'm not sure how to get around this. Sure, I could CAST as a 4000, but I'm going to lose data ...
Any thoughts?
Thanks
August 11, 2010 at 12:00 am

This was removed by the editor as SPAM
August 11, 2010 at 6:39 am

This was removed by the editor as SPAM
August 11, 2010 at 6:46 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

stewartc-708166 (8/11/2010)
Excel works with the Unicode character set.
The maximum size of a unicode column is 4000 bytes
If you check the datalength of the column, are there records that do indeed exceed 4000 bytes?
if so, check the contents of the cells when retrieved using MS Query.

Yeah, the max length of a value in the column is 5995, with several dozen others above 4000. Now when I find column in the spreadsheet that has the SQL data source in it, the length of the column is indeed the same length. So this makes me wonder, why can I do it this way, but not in the extract?

Thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

In the data conversion step, it's set to "unicode string [dt_wstr]" 4000. I tried to change to 8000 and it says values must be between 0 and 4000. Which data type would support this value?

Thanks!

Vantigate Valued Member Points: 72 More actions · Answer 3

Try changing the source column's type to varchar(MAX). It worked for me.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Try using the dt_ntext data type in ssis

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Randy Doub SSCertifiable Points: 5322 More actions · Answer 5

Had the same problem once. I ended up creating an Excel "template" with row 1 having my column headers and row 2 having formatted sample data. In the long text column I valued it with a 1024 character string. SSIS copies the template to the destination folder, populates it from a sql task, data conversion uses DT_NTEXT for the long text column as suggested earlier, then a VB.Net script task deletes row 2 (my format row).