SQL SERVER 2008 - SSIS Excel Destination

I am currently trying to use SSIS to export a large dataset into an Excel destination, however the issue that i have for one of my columns is that records within it exceeds that maximum 开发者_运维问答amount of characters allowed to export. Currently it is set to 255 using Data type DT_WSTR.

To give you a bit of background I had to use the data conversion tool for a few of my source columns to convert from String [DT_STR] to [DT_WSTR] which partially solved my problem by allowing me to export into excel, except for those records which exceeded the character limit. For now I have just setup a multicast and redirected those records.

Is there any way to get around this?

You need to use a memo data type for columns containing more than 255 characters.

Excel Destination

Saving memo (ntext) data. Before you can successfully save strings longer than 255 characters to an Excel column, the driver must recognize the data type of the destination column as memo and not string. If the destination table already contains rows of data, then the first few rows that are sampled by the driver must contain at least one instance of a value longer than 255 characters in the memo column. If the destination table is created during package design or at run time, then the CREATE TABLE statement must use LONGTEXT (or one of its synonyms) as the data type of the the memo column.

Memo Unicode text stream (DT_NTEXT)