February 13, 2003 at 11:36 am
I have a DTS that is transferring data from one server to another. I have two task defined: An Execute SQL Task and a Copy SQL Server Objects Task.
The Execute SQL Task deletes the data from the destination table and the Copy SQL Server Objects Task Appends the rows from the source server to the Destination.
I would like the the delete and copy to be rolled back if any of the steps fail, for example if the delete fails after deleting 100 rows it should roll back to my original number of rows on my destination server and if the Copy SQL Server Objects Task fails after copying only 200 rows it should roll back to my original 1000 rows in my destination database.
I have only checked the Rollback transactions on faillure boxes on both steps and the Use Transactions and Commit on successfull package completion boxes in the DTS package properties, but when I fail the package after it has deleted a few rows it does not rollback to what it was before I executed the DTS.
Any suggestions???
Thanks
Stéphan
February 15, 2003 at 9:33 am
Check out "DTS Transaction Fundamentals" in Books Online. This does a good job explaining the ins and outs of usong transactions in DTS. In particular you will see that the Copy SQL Server Objects task is one of the tasks that does not support transactions.
(mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\dtssql.chm::/dts_addf_tx_660j.htm)
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply