I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database.
The Execute SQL Task (named "SQL Update MY_STATUS flag") is using an OLE DB Connection Manager with a "Native OLE DB\Oracle Provider for OLE DB" Provider. All other task settings are default values.
The query is simple:
UPDATE MY_TABLE
SET MY_STATUS = 1
WHERE MY_STATUS = 0
(Note: This query succeeds when run in SQL Developer with a semicolon terminator. Here the terminator is omitted, because if a semicolon is included, an invalid character error is generated by SSIS.)
When I start debugging, the task begins execution and then hangs indefinitely, the package does not complete execution.
The debug output window shows this message:
Warning: 0x0 at SQL Update MY_STATUS flag: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if
available. No work was done.
(The Task behaves the same with the query configured as Direct Input, or as a Variable.)
What does this "Multiple-step" warning mean in this context? What must I do to allow the Execute SQL Task to succeed?
Best Answer
The reason the task was hanging was that I had run a separate update statement against MY_TABLE in a SQL Developer client window, and had not completed the transaction. Because the transaction was still open, the Execute SQL Task could not complete. Committing the transaction in SQL Developer allowed the SSIS package to complete, however, it still generated the same Warning message.
It appears the Warning message is harmless, though I still don't know how to interpret it.
Attention Microsoft SQL Server DBAs! The SQL Developer Oracle query window DOES NOT work like SQL Server Management Studio! DML queries are not implicit transactions, so and INSERT, UPDATE, OR DELETE statement leaves an open transaction unless you click the COMMIT or ROLLBACK button on the toolbar. Learn from my mistake.