I've added a Data Flow Task to a Foreach Loop in a SSIS package. I want each
execution of the data flow task to run in a separate transaction.

I've tried setting the transaction mode of the data flow task to "required"
and the loop to "supported". This works fine the first iteration of the loop.
Then, the task fails with "Cannot enlist in transaction. Distributed
transaction completed...".

It seems that the data flow task commits the transaction after it finishes,
but no new transaction context is generated by the loop container. How is
this supposed to work?

Setting the transaction attribute of the loop container to "required" of
course causes all iterations of the loop to run in the same transaction
context.

How do I generate a new transaction context for each iteration of the loop?

thanks,
Micke

I am curious as to why you have set the Data Flow task to be the
initiator of the DTC transaction (Required) and the parent task to be
the joiner (supported)?

Allan


"Micke" <Micke (AT) discussions (DOT) microsoft.com> wrote

Because I want to initiate a new transaction each time the data flow task is
executed. Any ideas on how to achieve this. I haven't found a way to do it by
setting transactional attributes on containers and tasks. Is there any way to
control the SSIS transaction context programmatically?

//Micke

"Allan Mitchell" wrote:

Have you tried just setting the "Required" on the Data Flow task seeing
as that is all you actually want to be in the transaction. The loop is
simply something which executes the task.

Allan

"Micke" <Micke (AT) discussions (DOT) microsoft.com> wrote