rollback transaction

Question

 

This is related to rollback transaction in SSIS. I have couple of stored procedure being called from Execute SQL Task.

My scenario is”

1. Truncate staging table A  (Execute SQL task)
2. Extract data into A from two source ( Data Flow task using ADO.net)
3.  Call  two stored procedures to merge into target (execute sql task)
4. If no error  update another table with  current timestamp and commit transaction

If there is any error anywhere in between say for example a network failure, I  want my package to not go to the next step and roll back everything.I also have used begin/rollback/commit within each store procedure,so if an error, it rolls back whatever was done by that sp. But the problem is, it comes out of sp and continues with the rest of the tasks in the flow. I have used “ retain connection”= true for Oledb connection. However when there was an error in the stored procedure it did not roll back the flow, it instead went ahead and updated the timestamp table. Any advise on how to rollback transaction in SSIS.

Answer 1

Why not move the transaction statements to SSIS:

2. Extract data into A from two source ( Data Flow task using ADO.net)
And are you using a ADO.Net in combination with an OLEDB connection?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Why not move the transaction statements to SSIS:???

And are you using a ADO.Net in combination with an OLEDB connection? -Yes I have OLEDB destination.

Answer 3

While SSISJoost has offered a solution approach, here is something further to consider if you haven't already looked at the transaction feature under tasks like Execute SQL. SSIS does offer the option to work with your transaction needs through property settings. Under transactions you'll see something called TransactionOption. Out of the box it defaults to "supported", but if its required by your app,  then switch supported to "required". This will assure that whatever transactions you have under that task are enrolled as a transaction outside of what your stored proc is managing. I can't tell from your post whether you're interested in managing the isolation level of your transactions (e.g.  read uncommited, repeatable read, chaos, etc.), but there is a property for that as well.  Here's a little more info about how to utilize the property in relationship to your flow control.

http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

As an aside, I am going to provide a troubleshooting url for mdtc -- frequently an issue when dealing with tranaction/isolation level settings.

http://msdn.microsoft.com/en-us/library/aa561924.aspx