A couple of months ago I did a post on Transactions in SSIS, but that solution requires enabling the windows service Microsoft Distributed Transaction Coordinator (MS DTC). What if you can't (or prefer not to) use that service?
Solution
You can use the Transact SQL transactions to accomplish the same result. Same example as before. I want to empty and refill a table with values from a CSV file, but I want to keep the old data when the refill fails. My package:
 |
| Example |
1) Container
Add a Sequence Container and drag the existing Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to it.
 |
| Sequence Container |
2) Start Transaction
Add an Execute SQL Task before the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: BEGIN TRAN MyTran.
 |
| Start Transaction |
 |
| BEGIN TRAN |
3) Commit Transaction
Add an Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: COMMIT TRAN MyTran.
 |
| Commit Transaction |
 |
| COMMIT TRAN |
4) Rollback Transaction
Add an other Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: ROLLBACK TRAN MyTran.
 |
| Rollback Transaction |
 |
| ROLLBACK TRAN |
5) Precedence Contraint
Change the Value property of the Precedence Contraint between the Sequence Container and the Rollback from Success to Failure. If something fails in the Sequence Container the Rollback command will be executed.
 |
| Precedence Contraint |
 |
| Failure |
6) RetainSameConnection
Now the most important thing. Change the RetainSameConnection property of the database connection from false to True.
 |
| RetainSameConnection |
7) The Result
That's all there is. Now you can test your package. You can open the CSV file in Excel to lock the file and fail the package.
 |
| The Result |
* UPDATE *
Added the optional transactionname in case you want to re-execute. See comment Arthur Zubarev.
I think by not using named transaction scopes
ReplyDeleteE.g. Begin Tran MyTran ...
Commit Tran MyTran / Rollback Tran MyTran
you are running into a risk of not being able to re-execute the package if it fails.
I think to recover you need dynamic tran names.
E.g. Begin Tran tr_232_232.
Can you verify if you can abort or fail the package in the middle so it does not reach one of the last steps and then restart it?
Hi,
ReplyDeleteIf I want to have an Execute SQL Task after COMMIT/ROLLBACK Tasks, can you please let me know how to go about. Though I tried using Execute SQL Task after the COMMIT/ROLLBACK Tasks , it does not proceeds further after COMMIT task. I changed the Precedence constraint to Success/Completion, but its not working. Any help is appreciated.
Did you connect the last Execute SQL Task to both the Commit and the Rollback? Did you use a Logical OR instead of a Logical AND in the Precedence constraint?
ReplyDeletei am following the same procedure as above but still not able to rollback on failure
ReplyDelete@Anonymous: You have to give me a little more details then just saying it isn't working. Contact me via the webform
DeleteStrange enough, I can not get to Rollback task also. If I enable "Begin Transaction Transname", the import block in not erroring out.
Delete@Ljubinka: Are you getting any errors? Is it a lot of data that you try to import?
DeleteThank u , It really help me to solve my issue :)
ReplyDeleteJoost - Your post saved me hours of pain, failure and frustration. As a rookie in SSIS and SS, I would have never imagined that there could be a simpler and neater approach. I hope that I can become an expert like you someday and make such great articles. Thank you very much indeed. You deserve a medal, from Obama himself. Check out the meme "give that man a medal". :)
ReplyDeletehttp://www.indianfunpic.com/wp-content/uploads/2013/07/Give-That-man-a-Medal.jpg
Hi - Just forgot to add, when you don't set retainSameConnection == true, you get the error - [Execute SQL Task] Error: Executing the query "COMMIT TRAN MyTran" failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
ReplyDeleteSetting retainSameConnection to true is the most important step (see 6), but thanks for the error message. Now everybody can google it :-)
DeleteHello- I have a similar setup but in addition to the above flows I also have a SQL task which inserts into an audit table before the first 'empty table' task and then updates the audit after the 'refill task' inside the same sequence container. I have 2 or more sequence containers in parallel like this doing different functional operations but the same audit inserts and updates. This does not work and gives an error as "[Execute SQL Task] Error: Executing the query "Rollback Transaction" failed with the following error: "Abort called not inside a transaction". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.".
ReplyDeleteI use different connections managers for Auditing SQL tasks and for other operations (like 'refill task' here). Audit table is in a Database1 and other operations target on a Database2.
Could you advice what could be going wrong? Should I be using DTC just because I am inserting into 2 different databases in the same package/sequence?
Thanks in advance.
This trick works for one connection manager at a time. Also make sure you have set the RetainSameConnection to true.
DeleteThank you very much for posting this design patter. It helped me very much. Works also wtih ODBC connection, so I could use it towards PostgreSQL.
ReplyDeleteHI, We did use "RetainSameConnection" property but still had the issue.
ReplyDeleteOk, we got the design working after much deliberation. The issue was due to the Greenplum driver not being able to understand what SSIS wanted it to do. We set the design as in my previous post and got an updated version of the driver. Set DTC on on the HAWQ server and there you go.
Also seemingly there was an 'gather autostats' switched to 'on' on the HAWQ server,this was causing SSIS to timeout waiting to start a fast load into a table. (thought i mention this)