OK, I have fought this problem for the last three days. I now bow to the extensive and superior knoweledge of this forum for help.
My problem KISSed to the max: I have a varchar(1000) column in a SQL table called OrderComments. I need to export it to an Excel file.
I have built a SSIS package with a Data Flow task.
The OLE DB Source External Columns has OrderComments as string[DT_STR] with a length of 1000. The Output Columns has OrderComments as string[DT_STR] with a length of 1000.
The output flows to a Data Conversion component. The Input Columns has OrderComments as DTR_STR with a length of 1000. The Output Columns has 'Copy of OrderComments' as Unicode text stream[DT_NTEXT].
The output from the Data Conversion component flows to an Excel Destination component. The Input Columns has 'Copy of OrderComments' as DT_NTEXT. This is mapped to s External Columns named OrderComments with a data type of Unicode String [DT_WSTR] with a length of 255.
I created an Excel file template with only the column name in the first row. No other formatting or anything is done to the file.
I cannot change the External Column in the Excel Destination to a DT_NTEXT, nor can I delete and re-add the column. It always converts to a Unicode String [DT_WSTR] with a length of 255.
Oh, please, great and wonderful Gurus of Mr. Bill, do you have any suggestions? I gotta be missing something elemental.
Thanks in advance,