November 18, 2012 at 6:16 am
Hi, I have an SSIS 2008 package which will call a stored procedure and store the data into a variable . My stored procedure is actually inserting data into a couple of tables and then selecting few fields as a result to the SSIS package.
Now, what I want is if my SSIS package fails for any reason, I want my package to rollback the data inserted into the tables in my stored procedure. Can any one please let me know how to achieve this ?
Many Thanks
November 19, 2012 at 7:12 am
There are a couple of ways you can do this:
1. You can have SSIS run under a transaction. Go to the package properties and set the transactionoption property under transactions to "required." Any step that you want as part of the transaction, set its transactionoption to "supported." If you just have one or two steps that need to run under transaction, set transactioption in the step that runs the stored procedure to "required" and any other step that needs to run under transaction to "supported." Keep in mind that SSIS transaction requires that a service is running (Distributed Transaction Coordinator) to use SSIS transactions. You also have to set your connection to "retainconnection."
2. You can just run the stored procedure under transaction by using "Begin Transaction" at the beginning and "Commit " at the end. Use a SQL "try-catch" so that if there is a problem, you can rollback the inserts when the "catch" is executed.
There are lots of online articles that discuss SSIS transactions and SQL transactions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply