How do I fix the multiple-step OLE DB operation errors in SSIS?

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

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

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

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.

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.

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

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á!