October 16, 2006 at 8:36 am
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.
October 19, 2006 at 8:00 am
This was removed by the editor as SPAM
October 19, 2006 at 9:57 pm
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
October 20, 2006 at 2:04 am
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.
October 20, 2006 at 9:39 am
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.
October 23, 2006 at 2:43 am
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!
October 23, 2006 at 10:12 am
It depends how much time you are willing to waste I think. SSIS is totally different than DTS. Totally.
May 2, 2008 at 3:55 am
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?
October 16, 2009 at 10:28 am
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
October 17, 2009 at 4:35 am
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 ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 23, 2012 at 6:26 pm
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
August 23, 2012 at 7:33 pm
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.
August 24, 2012 at 5:37 am
That's a good idea. Will give it a shot.
Jamie
March 18, 2013 at 12:32 am
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)
August 19, 2013 at 4:20 am
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