I am writing this post for all the Microsoft pundits out there whose answers to a question such as “How do i export data to an excel spreadsheet using ssis?” is the following “it is very easy, just drop the control to your working area and run the package”.  Guess what it is not always that straightforward and everything changes as soon as you are trying to export a text column from your database. You ended up finding hacks since the tool (SSIS) does not do what it is supposed to do well: “Export my damn data to excel no matter what it is.” Also, the last time i checked “text” what a common data type in databases, Why would I even need to convert text?  why doesn’t the tool handle that?

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

The error above does not tell us s??t. After dealing with the error above for almost half a day, i finally found a solution (Honestly i would not call it a solution but a hack). Let’s rewind and see how i got the error message in the first place.

I am trying to just export SQL data to an excel spreadsheet using SSIS (sql server 2005, BIDS – business intellingence development studio). My data contains a text field and i could not for sake of it export that text field to the excel spreadsheet. I have used everything (database conversion , derived column ….) without a success. Then i recalled reading something about the limitations of the Jet Engine which is the Microsoft Technology used to communicate with these excel spreadsheets.

The problem is that the excel destination task is reading the excel sheet and decide what the type of the fields should be (based on the field's content). This is stupid because why is it that the task is checking my excel sheet when the later does not have any data yet. For example if you have a column on the excel sheet that has a number, the excel destination task may expect you to send an integer to this column and so on

So if you want to send a text field from your database to an excel spreadsheet just follow the following procedure:

On your Excel spreadsheet template find the column that will contain the text data, go ahead and enter a very large text to it. YOU WANT THE JET ENGINE TO SET THE TYPE OF THIS COLUMN TO "TEXT".

-You are tricking the jet engine to think that it must assign a text type to this field. Personally i do not like workarounds but this hack has worked for me.

now run your DTS package, you should not have any issue.

Here is a sample  excel sheet to give you a better idea:
http://ycsoftware.net/custom/dts_ole_sample_file.xls