I am trying to create a simple package with multiple Sequence Containers,
each with its own Transaction. So far, I have not been able to get ONE
Sequence Container to use a transaction.

The Container has two tasks, first is a TRUNCATE Table SQL task, followed by
a simple Data Flow task.

The TransactionOption of the Container is set to Required, and that of the
tasks is set to Supported. The connections are all OLEDB; the 'source' is SQL
2000, and the destination is SQL 2005/SP1. Both Servers run Win 2003.

When the package runs in Desing mode, I get the following ouput:

<<Information: 0x4001100A at Sequence Container: Starting distributed
transaction for this container.>>

Then, it stops for a while with the container and first task colored yellow,
then I get this:

<< Error: 0xC001402C at Package1, Connection manager
"<MyDestinationCOnnection>": The SSIS Runtime has failed to enlist the OLE DB
connection in a distributed transaction with error 0x8004D00E "The
transaction has already been implicitly or explicitly committed or aborted".

and the package fails.

I have seen other posts where people get their Sequence Containers to run
within a MSDTC transaction, but mine don't.

To work around, I set two SQL tasks in each container before and after my
data flow, with "BEGIN TRAN" and "COMMIT TRAN" respectively. However, to make
this work, all the Sequence Containers need to be chained along, thereby
loosing the ability of parallel processing.

Allan Mitchell: if you are out there, I would love to hear what you have to
say on this one. I borrowed my 'work-around' from page 299 of your book.

Thanks in advance.

Todd C

To work