Hello,

Greeting to all the Visitors!!!

I am currently facing a problem in rolling back the DTS Package.

I have 4 tasks in my DTS Package.

I used 4 "microsoft OLEDB Provider" ( connection )and "transform data task" ( task ) for transfering from one database to another database.and 2 "Execute SQL Task " ( Task ) for delete operations.

In easy words, The first transform data tasks are used to query and transfer 1 to 5 records of 10 records from source table to destination and similarly the second transform data task used to tranfer 1 to 5 records of 10 Records from source table to destination table.

After transfering there are two other Execute SQL tasks which are used for deleting 1 to 5 records from both tables.

The source tables and destination tables are in different databases.I tested the package by first clearing the destination table and executing the package.I get the result as expected.

THE PROBLEM IS I AM NOT ABLE TO ROLLBACK in case if there is any error.

I tried to make the "transform task 2" to fail and in which case the task one to be successful.Since the task 2 fails the Package has to roll back.

I tried several options provided in the work flow properties and Package properties but I am not able to rollback. So, kindly provide any solutions.

Also, while I was trying this option.

That is when I checked the following properties and executed I get the following error.

Connection 'MySecondConnection' for Task DTSDataPump does not support joining distributed transactions or failed when attempting to join. New transactions cannot enlist in the specified transaction coordinator.

Package Properties:
Logging tab : Checked the " Fail Package on first error"
Transactions : Checked Use Transactions and commit on successful tranactions and tansaction option as serializable.

Work flow Properties :
Checked Join transactions if Present
Checked ROllBack on failure
Checked Execute on Main Package thread.

I executed the Package by placing the databases in the same server and also different servers.But I still get the same errors.

References:
http://support.microsoft.com/kb/q279857/
http://technet.microsoft.com/en-us/l...9(SQL.80).aspx

Kinldy email if you find the soluition.

Thanks in Advance!
r1a2m3