[sql-server] How do I fix the multiple-step OLE DB operation errors in SSIS?

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.

For me the answer was that I was passing two parameters to and execute SQL task, but only using one. I was doing some testing and commented out a section of code using the second parameter. I neglected to remove the parameter mapping.

So ensure you are passing in the correct number of parameters in the parameter mapping if you are using the Execute SQL task.

I hade this error when transfering a csv to mssql I converted the columns to DT_NTEXT and some columns on mssql where set to nvarchar(255).

setting them to nvarchar(max) resolved it.

'-2147217887' message 'IDispatch error #3105' source 'Microsoft OLE DB Service Components' description 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.'."

This is what I was also facing. The problem came from the fact that I changed my SQLOLEDB.1 provider to SQLNCLI11 without mentioning the compatibility mode in the connection string. When I set this DataTypeCompatibility=80; in the connection string, I got the problem solved.

Take a look at the fields's proprieties (type, length, default value, etc.), they should be the same.

I had this problem with SQL Server 2008 R2 because the fields's length are not equal.

This error is common when the source table contains a TEXT column and the target is anything other than a TEXT column. It can be a real time-eater if you have not encountered (or forgot!) this before.

Convert the text column to string and set the error condition on truncation to ignore. this will usually serve as a solution for this error.

This error will also occur when trying to do an insert and a field is coded not null and nulls are trying to be inserted.

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 

In my case, the problem was setting the variable of the Execute SQL Task, in parameters mapping the parameter name, (in OLEDB must be the position of the parameter that you call in the stored procedure), I had 1, but the first parameter starts in 0, so I changed it and voilá!

This issue will come mostly due to empty rows at the end of the file, remove those and run the job.

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%.

Also check if the script has no batch seperator commands (remove the 'GO' statements on a single line).

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.