SSIS OLEDB error when trying to export long unicode string to Excel destination

Question

My Data Flow task in SSIS BIS 2008 contains a DB query source with a NVARCHAR column returning a constant string of 688 characters, which I am trying to insert into an Excel destination column when filling in a precreated template.

When all column types of the DB source and Excel destination are DT_WSTR of max. 255 chars all works fine. But changing the input column of the Excel Destination (in Advanced properties) to NTEXT causes package pre-processing errors

"[Excel Destination [97]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.[Excel Destination [97]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid. [SSIS.Pipeline] Error: component "Excel Destination" (97) failed the pre-execute phase and returned error code 0xC0202025."

This is regardless of what is the source for this column - directly the DT_WSTR from the database or DT_NTEXT from a Data Conversion task inserted in between.
The best consistent solution I could find online was to reboot the machine - not promising.

Is there a way to export a single UC string of length > 255 to an Excel destination?

Answer 1

From all research it seems this is not possible. The final workaround is to truncate the text to be inserted into Excel to max. 255 characters. Did not impress the business but worked fine in the end.