I need to output some string data from a couple of columns from a SQL Server OLE DB source into an Excel destination, where the source data type is nvarchar(max). I am using the template approach for the Excel destination, copying across the template and then using a SSIS data flow to pump the data in. My problem is that the external column metadata of the two columns defaults to DT_WSTR with length of 255. The trouble is that I am unable to change the external data type to DT_NTEXT. I try to set this via the Advanced Editor, exit the data flow but the change doesn't stick and it just goes back to DT_WSTR.
In the mappings of the Excel Destination, there is a mapping of DT_NTEXT from the input column (from the data flow pipeline) to DT_WSTR in the Excel external column. Consequently when I run the data flow it fails immediately on a validation error.
I understand from Microsoft that Excel only supports the following SSIS data types:
Numeric double-precision float (DT_R8)
Currency currency (DT_CY)
Boolean Boolean (DT_BOOL)
Date/time datetime (DT_DATE)
String Unicode string, length 255 (DT_WSTR)
Memo Unicode text stream (DT_NTEXT)
What I can't understand is how to make the two external columns be DT_NTEXT instead of DT_WSTR. Why does it ignore my change made via the Advanced Editor. Is there some other step required to ensure the column mapping is DT_NTEXT to DT_NTEXT?
Many thanks in advance.