CODEWITHSUNDEEP
sql-serveroracle-databasessis
needshelp
needshelp

Reputation: 11

Update statement to Oracle failing in SSIS Execute SQL Task

I have to execute some Update statements from SSIS to Oracle which I cannot put into a stored Proc. This statement runs fine in Oracle, but I get error when executing from SSIS. I am using an Execute SQL Task with properties SQL Source Type = Direct Input, BypassPrepare = True. On executing the task, it just hangs for 20 minutes or so. Then I clicked on stop debugging.

UPDATE   Table1 R
  SET   R.Column1 =
           (SELECT   SUM (Column2)
              FROM   Table2 M

             WHERE                                
                  M.Column3 IS NULL AND M.Column4 = R.Column4)
WHERE   EXISTS (  SELECT   Column4 AS Column4
                    FROM   Table2 M

                   WHERE                          
                        M.Column3 IS NULL AND M.Column4 = R.Column4
                GROUP BY   Column4)   `

Upvotes: 0

Views: 2850

Answers (1)

peterk411
peterk411

Reputation: 236

When this happened to me, it was because I had left a transaction open. I had been using SQL Developer to reset column values in the target table during testing. (SQL Developer doesn't use implicit transactions by default.)

Here's the detail:

SSIS PL/SQL task hangs with message “Multiple-step OLE DB operation generated errors.”

Upvotes: 3

Related Questions