Subscribe to this blog
Briefcase
Print
If you experiment at all with transactions that are built into SSIS you will discover that they are highly flawed. For example, if you have a transaction running on an entire package some tasks may not rollback on a failure. A file system task is one of the major culprits that on a failure will not rollback the file operation it is performing. I wish I was writing to give you a solution to that problem today but I’m actually writing to show how you could use a typical database transaction across multiple tasks in SSIS.
To demonstrate this I designed the package below. The #1 Execute SQL Task starts the transaction and #3 commits it. It sounds good but when I run this package the value of 2 is still inserted into my table.
To make this work how you would think it should you simply need to change one property in the connection manager properties. Select the connection manager used in these tasks and change the property called RetainSameConnection to true.
When you run the package again no values are inserted. This is exactly what I wanted.
Posted by Anil Kumar Kubireddi on 8 March 2010
nice one...help full.
Posted by robink on 8 March 2010
Really a nice one.
Posted by Ritesh on 9 March 2010
Hi Devin
Thx for this update, but when I try this in one of our SSIS which is used to convert Access db into SQL. It does not work as you have mentioned.For example,I have SSIS which has following task define
A->B->C->D->E. All this task insert data into SQL from Access. Due to error SSIS failed at task D, but it still insert data into tables A,B,C which should have been rollback.
I have connection as below:
connAbc pointing to ABC db on SQL
connXYZ pointing to XYZ db on SQL
connLMN pointing to LMN AccessDb
The above task insert data into ABC DB on SQL server. I configure "reatainconnection" = true for connABC and all of the task's "transactionsupport" = supported, infact I try with "required" as well.
Can you help me figure out whats wrong here. Should you need any clarification pl let me know.
Thanks in advance
Ritesh
Posted by Ritesh on 9 March 2010
in addtion to above I am not using any loop container
Posted by knight_devin@hotmail.com on 9 March 2010
Try this method www.sqlshare.com/using-transactions-in-ssis_39.aspx