How to insert a cloumn with more than 255 characters to excel in SSIS

Question

I have a table column with length more than 255 characters and I need to insert this value into excel. When ever the column value exceeds 255 characters, I am getting "Excel Destination [15]: Truncation may occur due to inserting data from data flow column "Copy of Column 0" with a length of 500 to database column "column1" with a length of 255." error at excel destination component.

Please help me to find a way to insert a column with more than 255 characters into an excel.

The only way I could get this to work is to put a value with over 255 characters in my Excel document manually. Then, in SSIS, that changes the destination output column data type to DT_NTEXT which can handle over 255 characters. However, I don't know of a good way to overwrite the long string value after the data is written to the spreadsheet. — gannaway

Peter_R Peter_R · Accepted Answer · 2013-12-23T17:14:29

I'd imagine you can overwrite the setting through the Advanced Editor in SSIS.

Right Click the Excel Destination > Show Advanced Editor > Input and Output Properties

Excel Destination Input > External Columns > Column 1 (Change Length to be 500).

This should stop the error.

How to insert a cloumn with more than 255 characters to excel in SSIS

1 Answers