Ever run into this error when building an SSIS package? I did, just last week. Here’s the full text of the error:
SSIS package "MyPackage.dtsx" starting.
Error: 0xC002F210 at Cancel Orders, Execute SQL Task: Executing the query "
DECLARE @SCRNum VARCHAR(6), @ITSName VARCHAR(30),..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Cancel Orders
Warning: 0x80019002 at Cancels: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at MyPackage: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The program '[1124] MyPackage.dtsx: DTS' has exited with code 0 (0x0).
A search on “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.” gives all sorts of forum pages about Script Tasks, ODBC errors, and parameter mapping problems. None of which was my actual problem.
I had an Execute SQL Task with multiple parameters being passed in from package variables. Here are a pair of screenshots similar to what I was looking at. First, the parameters page:
Now the code source from the General Tab:
When I first coded this task, it succeeded without issue. I had the parameters mapped properly and had the code correctly working. I test my SSIS packages by executing each individual task as I finish them. This task was near the beginning. As I got near the end, I realized I’d forgotten a few things in this specific Execute SQL Task. So I rewrote the code in SSMS, tested it there, then ported it over to the Execute SQL Task.
After the port, every time I tested the package, it failed on this step, the same step that had succeeded was now throwing this multiple-step OLE DB error. I spent a full day Googling (with results that did not help me) and banging my head against the desk because everything I tried did not work. No matter how many times I tested the code in SSMS, it worked. But the package still failed. The parameters never changed in this task, so they couldn’t be the problem unless they’d gotten corrupted somehow. So I deleted the variables and parameters, recreating them all from scratch.
And got the same error.
Finally I noticed something in my SQL Source that hadn’t been there before. “USE
Since no one else seems to have written about this particular error solution, I’m posting this on my SQL blog in the hopes it will save someone else two days of desk-banging migraines.
One Response
Thanks
This may be old but great tip – found this after searching for hours. In my case, I had the USE and GO statements to switch the database. I simply had to remove the GO to get it to work.
Great tip and thanks again.