Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Mike Caddy
Mike Caddy
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 2
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.
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10095 Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
kenambrose
kenambrose
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 658
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



Mike Caddy
Mike Caddy
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 2
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.
kenambrose
kenambrose
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 658
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.



Mike Caddy
Mike Caddy
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 2
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!
kenambrose
kenambrose
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 658
It depends how much time you are willing to waste I think. SSIS is totally different than DTS. Totally.



born2bongo
born2bongo
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 351
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?
bfarrelly
bfarrelly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 64
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8400 Visits: 19504
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 ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search