Integration Services: Multiple-step OLE DB operation generated errors

Question

I'm attempting to make a DTS package to transfer data between two databases on the same server and I'm getting the following errors. Iv read that the Multiple-step OLE DB operation generated error can occur when you are transferring between different database types and there is loss of precision, but this is not that case here. How do I examine the column meta data?

Error: 0xC0202009 at Data Flow Task, piTech [183]: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC0202025 at Data Flow Task, piTech [183]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "piTech" (183) failed the pre-execute phase and returned error code 0xC0202025.

Answer 1

You can use SELECT * FROM INFORMATION_SCHEMA.COLUMNS but I suspect you created the destination database from a script of the source database so it is very likely that they columns will be the same.

Some comparisons might bring something up though.

These sorts of errors sometimes come from trying to insert too much data into varchar columns too.

Answer 2

The source db and destination db are entirely unrelated and one was not generated from a script of another. Ill have a look into the column lengths, but I haven't seen any warnings about it in the stack.

Answer 3

This query should identify columns that are potential problems...

SELECT * FROM [source].INFORMATION_SCHEMA.COLUMNS src INNER JOIN [dest].INFORMATION_SCHEMA.COLUMNS dst ON dst.COLUMN_NAME = src.COLUMN_NAME WHERE dst.CHARACTER_MAXIMUM_LENGTH < src.CHARACTER_MAXIMUM_LENGTH

Answer 4

Hi,

I had a similar issue when i was transferring data from an old database to a new database, I got the error above. I then ran the following script

SELECT * FROM [source].INFORMATION_SCHEMA.COLUMNS src INNER JOIN [dest].INFORMATION_SCHEMA.COLUMNS dst ON dst.COLUMN_NAME = src.COLUMN_NAME WHERE dst.CHARACTER_MAXIMUM_LENGTH < src.CHARACTER_MAXIMUM_LENGTH

and found that my columns where slightly different in terms of character sizes etc. I then tried to alter the table to the new table structure which did not work. I then transferred the data from the old database into Excel and imported the data from excel to the new DB which worked 100%.

DON