I have DTS with 3 steps

Step 1 Execute SQL insert into table A
Step 2 Execute SQL update table B
Step 3 Execute SQL delete from table C

if Any of the step fails I want rollback all the changes.

Presently
if step 2 fails ,insert into table A from step 1 COMMITED ?!!!

I tried all options of package properties with no success.

How commit changes in A,B,C only if all 3 steps complited with success ?

Thank you

Alex

You do not tell us all the things you tried but this works for me.

1. Enable transactions at the package level (Package Properties)
2. On Success between each task
3. In the workflow properties for each task (check "Join transaction if present","Rollback Transaction on Failure")
4. Make sure MSDTC is started

NOTE: Not everything supports joining a transaction


--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org