error 0x80040E21 "Multiple-step OLE DB operation generated errors...."

  • I've used the Import / Export wizard to dump a tables from a SQL 2005 db to a series of Excel sheets, however when I try to import this data back into a SQL 2005 DB I get the error above.

    Full error:

    An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Cannot create an OLE DB accessor. Verify that the column metadata is valid.

  • This was removed by the editor as SPAM

  • there was an answer to this exact question recently on the microsoft ssis forum.  go there and search for mulit-step or similiar and you should find it.  IIRC it is a data-typing mismatch somewhere in your processing

  • Response posted on the "microsoft.public.sqlserver.integrationsvcs" newsgroup

    -------------------------------------------------------------------------------------------

    cherif@lockwoodtechnology.com wrote:

    > When I receive this type of error, it's usually a sign of incompatible

    > data types/sizes. I know Excel sometime will automatically convert a

    > character data to numeric data which could be causing a problem with

    > the import...

    >

    > Hope this helps.

    >

    > Cheri

    >

    Eureka, I think you may have something, I've just added a Data Conversion node and changed the type from Double Precision Float to eight byte signed Integer and it seems to be working.

  • it pays to remember that SSIS is very type-sensitive and will never do implicit conversions, where DTS did a ton of them for you.

  • Maybe I should have done the SQL 2005 training, however having picked up dts on my own I hoped that SSIS would be similar, the error messages are far from helpfull!

  • It depends how much time you are willing to waste I think.  SSIS is totally different than DTS.  Totally.

  • I have raised a new topic on this problem, but haven't been able to resolve it. I have just read this thread tried changing my numeric formats in line with the suggestion above.

    It has made no difference to my error message, but perhaps if I describe the data conversion before the excel destination, it might trigger a thought with someone.

    After previous difficulties, I made all my text columns into Unicode text streams. I have some date columns and these are all database timestamp. My number columns are eight-byte signed integers, single-byte unsigned integers or two-byte signed integers.

    Failing this, is there any way to find out which column(s) is/are causing the 'Cannot create an OLE DB accessor' error?

  • Just been through this and SSIS is very particular when reading from Excel. It samples the 1st 8 rows and determines datatypes from that sample. As we all know, it is tricky changing data types in SSIS so I found it easier to force the datatypes to be correct in the originating excel. So, rather than hope my datefield column happened to have more than 4 date values (if say the sample had 5 nulls, SSIS would assume string) I faked up the 1st 8 rows. In this way, I could fill out the fields to ensure the data types were right and truncation was avoided.

    Remember, 1st 8 rows are sampled, easy to fake up

  • I just (yet again) ran into this Excel 'feature' and it managed to irritate me just as much as it always has.

    This time, however, I tried a different solution: I added calculated columns to the right of the main data that forced the datatypes of the data to the left.

    So - if column A contains a 1, but might also contain non-numeric data, add a calculated column to force it to be read as text and use that in your import (=Text(An,0)).

    Note - this worked within a VBScript import of Excel data. Not tried it in SSIS, but should work ...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Same error here... very odd

    OLE DB Source [11]] Error: SSIS Error Code DTS_E_OLEDBERROR.

    An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available.

    Source: "Microsoft SQL Server Native Client 11.0"

    Hresult: 0x80040E21

    Description: "Multiple-step OLE DB operation generated errors.

    Check each OLE DB status value, if available. No work was done.".

    Will try rebooting the machine, because according to the IDE, no errors occur but obviously during the processing an error does occur but the IDE ignores it. Question is, why does it do that?

    In my package, which ran fine in 2008, I take two columns, separate them with a comma and delimit them with a pipe. I place this string into a text file. The best I can figure it that the comma and the pipe together violate some sort of ANSI7 rule and cause unicode to come into play inside the 2012 IDE.

    Ideas?

    Jamie

  • One way to be certain that SSIS will process data elements with exactly the data type you want is to make all the explicit conversions yourself from a "type controlled" import.

    For example, read all the data in as text from Excel. Then immediately convert each data element explicitly (perhaps to brand new columns) as desired in your data flow. Then you can be sure that all downstream tasks or components you apply to the data flow will use exactly the data types you expect.

  • That's a good idea. Will give it a shot.

    Jamie

  • Hi the below steps might help to solve issue more quickly.

    1.Use the OLE DB Destination.

    2.Use the "Table or View - fast load " as data access mode.

    3.Check “Keep nulls” and “Table lock” property, and keep the other properties unchecked.

    4.Map the new converted columns to SQL destination table columns.

    Thanks,

    Hemanth(CTS)

  • this worked for Me but didnt understand what was the problem

    Was anyone able to find it to avoid this again

    ------------------------------------------------------------------------------------

    Ashish

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply