Transactions and Loops in SSIS
-
Hey everyone,
I have a question that I have not been able to find an answer to (I have extensively Googled) having to do with transactions and loop containers in SSIS:
How do transactions work with For Loop containers and Foreach Loop containers?
In my 70-463 Training Kit (pg. 330) it says: "If you set the TransactionOption property of a Foreach Loop container or For Loop container to Required, a new transaction will be created for each loop of the container."
This makes sense to me if the loop container is the only object in the package with TransactionOption = Required. But what if a parent container, or the package itself, also has TransactionOption = Required? Does the loop container still create a new transaction for each loop? Or does it join the transaction of the parent container or package? If it joins the transaction, is each iteration of the loop a part of the one, overarching transaction?
My understanding of TransactionOption = Required is "If a transaction already exists, join it; if not, start a new transaction" (pg. 329). Based on this definition, I would expect a loop container to join the transaction of the parent container or package. But this seems to contradict what my book says about loop containers.
Here is an example diagram from Microsoft's documentation of what I am talking about:
I am wondering if anyone has a good explanation of what happens if the Package has TransactionOption = Required, and one of the Foreach Loop Containers has TransactionOption = Required.Any help is appreciated!
-
Hi,
From my tests, if the parent package TransactionOption is set to Required and the For Loop (or Foreach) TransactionOption is set to Supported, the For Loop operation will be involved in one transaction.
See the attached POC, using a For Loop (bad solution name, sorry) that has a parent package with the TransactionOption set to Required and the For Loop to Supported, and the For Loop executes a child pack that inserts a simple row to a list of tempdb tables. When one (the third one, to be exact, as you can see in the child pack "Raise Error" precedence constraint) of the child pack executions fails, all the inserts are reverted and the tables are empty:
select * from dbo.[0]
select * from dbo.[1]
select * from dbo.[2]
select * from dbo.[3]
When the TransactionOption of the parent pack is set to Supported, then the Successful inserts are kept.Best regards,
Marius
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply