Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

error 0x80040E21 "Multiple-step OLE DB operation generated errors...." Expand / Collapse
Author
Message
Posted Monday, October 16, 2006 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 08, 2008 6:27 AM
Points: 3, 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.
Post #315637
Posted Thursday, October 19, 2006 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, 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!
Post #316608
Posted Thursday, October 19, 2006 9:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 71, Visits: 473
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


Post #316819
Posted Friday, October 20, 2006 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 08, 2008 6:27 AM
Points: 3, 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.
Post #316843
Posted Friday, October 20, 2006 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 71, Visits: 473
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.


Post #316989
Posted Monday, October 23, 2006 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 08, 2008 6:27 AM
Points: 3, 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!
Post #317234
Posted Monday, October 23, 2006 10:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 71, Visits: 473
It depends how much time you are willing to waste I think.  SSIS is totally different than DTS.  Totally.


Post #317364
Posted Friday, May 02, 2008 3:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 3:12 AM
Points: 157, Visits: 349
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?
Post #494104
Posted Friday, October 16, 2009 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2011 3:59 AM
Points: 4, Visits: 59
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
Post #804353
Posted Saturday, October 17, 2009 4:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #804674
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse