Hi

I have a sequence container with TransactionOption = Required. In this
container there is a number of task with TransactionOption = Supported and
they therefore join the transaction started by the sequence container. This
all works fine.

Outside this container I have another task, which has TransactionOption =
Supported. As there is no transaction to join, this task should not run
within a transaction.

Nevertheless I get this error message, when the task is run (where
EndPackage is the name of the task):

-->
Executing the query "EndPackage" failed with the following error:
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
<--

The error message indicates that it tries to join a transaction, but it
shouldn't... Or is there something I've misunderstood?

If I alter the TransactionOption on "EndPackage" to "Required", this task
works, but then the problem is merely deferred to the next package in the
sequence - and there are several - and as the error also occurs in the
validation phase of subsequent packages, it is not really an option.

Regards

Further research discovers that the problem is related to logging. When
logging with the SQL Server Provider is enabled, then the problem is as
described. When logging is turned off - or one of the other providers is used
- it works.

The question is now if this is an product bug or "works as designed" - or is
there a workaround to the issue? (and yes, we do need logging!)

"Martin Schou" wrote:

Martin:
This is not a solution, but a question:

I too am working on a package which contains Sequence Containers but I
cannot get them to run with TransactionOption=Required and all its member
tasks with TransactionOption=Supported.

When I run the package, it Validates the Seq. COntainer, executes the first
task (a simple TRUNCATE TABLE statement in an Execute SQL Task) but when it
gets to the Data Flow task, which is supposed to load data from another Db
(on the same server) it hangs up, and also locks up Visual Studio after a
while.

The TRUNCATE task and DataFlow tasks run fine if not contained within the
Sequence Container.

I suspect I have some wrong settings on my Component Services under Admin
Tools. (Designing on an XP/SP2 machine with SQL 2005 Developer/SP1)
Start >> Programs >> Administrative Tools >> Component Services
Then open the Component Services node and the Computers node.
Right click on My Computer and choose Properties.
Click on the MSDTC tab. What settings do you have there. What settings do
you have under the Security Configuration button?

Any thoughts out there anyone?

Thanks in advance.

Todd C

"Martin Schou" wrote:

Try using a Delete statement instead.

Truncate table cannot run within a transaction. Transactions use the
database transaction log in order to enable rollback, and since Truncate
Table don’t log the deletion of data, the statement cannot participate in the
transaction.

"Todd C" wrote:

Hello Martin,

What?

TRUNCATE can exist in a transaction. Truncate does also log the deletion
of data albeit not individual rows.


Allan

Hello Allan

It's correct that Truncate table can exists in a transaction and supports
rollback in T-SQL, I was a little too quick on that. Thanks for your reply on
this.

But how do I enable this in a SSIS package as described, i.e. a sequence
container, with SQL Task containing a truncate, followed by a data flow task
importing data. To me is seems that MSDTC can't handle this in a transaction.

Regards Martin



"Allan Mitchell" wrote:

Guys,
Thanks for the input. Here is a little more info on my issue:

My target Db, where the transaction is occuring, is set to "Simple Revocery"
option. Would this make a difference?

I have been able to get it to run inside a transaction by explicitly
starting a native transaction with an Execute SQL task that has "BEGIN
TRANSACTION" and onother on the other end with "COMMIT TRANSACTION". However,
since there will be up to 25 'sets' of truncate and loads within the same
package, and since the Data Source Connection needs
RetainSameConnection=True, I end up needing to string the sets one after
another, otherwise, they will all be within the same transaction. But by
strining them along one after another, I loose the parallel processing
capabilities.

There HAS to be a way to use the Transaction capabilities of a Sequence
container to make this work.

I have noticed that even though both connections are servers, when I test
run the package in design mode, it uses the MSDTC of my local machine to
handle the transaction. Do I need to do something as stated in my ealier post
to make my local MSDTC handle this correctly?

Thanks in advance.

Todd C