An OLE DB error has occurred. Error code: 0x80040E21. Error: Cannot create an OLE DB accessor SSIS

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

 


  • Julio Moncisbays

    Thanks for your post, it helped me a lot!

  • prateep

    Same thing i did, problem not solved

  • John

    To the author:

    Dude, you spoke my mind. I never understood why does a text field needs so many additional transformations when it comes to using excel destinations.

    I have a question for you and i hope it makes sense. I have a stored procedure that returns a column with a huge string in each row. When i use an OLE DB source and check the external and output column datatypes in the advanced editor, the datatype is displayed as dt_ntext. i created the excel table with the destination column as a longtext datatype. Now what am i missing that i need to do to export this huge strings from the query to the excel sheet? I tried Data Conversion, Derived Columns. No luck and the error messages tell me no s@@t about it. Please help

  • http://ycsoftware.net admin

    1. Create the destination excel sheet first
    2. On the first row of the excel sheet you need to add dummy data.
    3. Still in the first row, add a large random text to the column that will eventually contains the large text.

    What happens is that the stupid OLE read the excel sheet and decides what data types should go there instead of the other way around.

    Hopefully that makes sense.

  • McGee

    Thanks… Your “solution” worked for me…

  • reddy

    Hi boss thank you!!!!! its working fine for me i am also facing lot of problems before your ans,but one question my side we are manully large text add the excel destination but instial time we add, but every time execution the data we keep the excel sheet