Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

rolling back data in SSIS package Expand / Collapse
Posted Sunday, November 18, 2012 6:16 AM


Group: General Forum Members
Last Login: Monday, February 9, 2015 1:26 PM
Points: 10, Visits: 72
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
Post #1386039
Posted Monday, November 19, 2012 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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.
Post #1386384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse