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,
What I do in a smiliar situation is output data to a flat file Out.txt and then rename the txt using either a script or a file system task and it works consistently.
Amazing! I KNEW Will's answere would not work, come on, just rename a file? But it really worked! Thanks Will!
For my own edification, and because is seems more "professional", I would like to understand Jacques answer a little better. I tried to apply a SQL task against the Excel connection, but the only selections I had available were for an OLE DB connection, which I could not figure out how to apply to an Excel File. Jacques, Could you please elaborate on this?
This forum is awesome! Thanks to all!
The connection manager for excel uses the Excel Jet Driver. What this does when you create your source and destinations is sample the first 8 records and based on what it finds in the column sets your external columns accordingly.
Even if you have set the column lengths in the Excel file using SQL, you may still find that SSIS fails where the data in your spreadsheet isn't of a length that you've specified (i.e. If you've set a column to be DT_NTEXT yet in your spreadsheets first 8 rows there are no column lengths longer than 255 characters).
There are options to deal with this
Issue & registry config instructions in slightly more detail