Transactions and Execute Package Tasks

[harish2]

Hello

Has any one had any experience in configuring transactions in SQL Server 2000 with the Execute package task?

I have serveral packages which encapsulate calls to stored
procedures. Each package has its own connection to the
same SQL Server.

I use another package to chain all of these other packages
together.

What I would like to happen is if a package fails all the
other packages roll back their work so nothing is
committed.

In the controlling package I have tried to set the
workflow properties
Join transaction = true
Rollback transaction = true
Fail package on step failure = true

I have also set these in the individual packages
themselves. But when I run it the individual packages
commit even if one of them fails. Or I get a message
saying that a step could not join the transaction ( MS DTC
is running).

So far the only way I can get the transaction to work is
If I execute in main thread is set for each step. But
this slows everything down.

Does anyone have any suggestions.

Thanks in advance

Harish