.
Ok So we are all familiar I think with MSDTC transactions but what if we want to implement a good old native transaction?
BEGIN TRANSACTION
<code>
COMMIT/ROLLBACK TRANSACTION
Here is how you do it.
Given a piece of workflow that looks like this
Reading from left to right we start a transaction, insert some good data and then we come to the Pivot Task. It is this task that will decide if we rollback or we commit.
The only thing we need to do is make sure that each ExecuteSQL Task uses the same connection manager and on that Connection Manager we set RetainSameConnection = true
The question that immediately pops up here is "So where is the ROLLBACK TRANSACTION on failure of the Pivot Task?"
Let's make sure the Pivot task fails. Well we are are lucky in that a ROLLBACK is issued for us. Here is what happens in Profiler.
BEGIN TRANSACTION
go
INSERT A VALUES(1)
go
INSERT A VALUES('A')
go
ROLLBACK TRANSACTION
go
Click to read this topic3/28/2006 6:11:58 AM - DarrenSQLIS
Click to read this topic11/6/2005 1:29:57 PM - AllanMitchell
