How to rollback the tasks into a package 1

[garpale]

I have a DTS package, which has some tasks. Each task is suppose to copy the data from an excel sheet to a table in the database. In case we encounter any error in the execution of any step all the data copied in the previous tasks in already populated. We want to commit the changes only when all the steps are executed successfully. Please tell me if there is any way to handle this.

I tried to use some options into the 'Package Properties'...they didn't work!

Thanks in advance.

 

[JoeSack]

Hi,

In addition to the main DTS package property, each task needs to be explicitly joined to the transaction (which also requires Distributed Transaction Coordinator to be running).

For each task in the transaction, right-click it and
select workflow->workflow properties. On the
options-tab, enable the checkboxes 'Join transaction if present' and
'Rollback transaction on failure'.

If you do not or cannot do this, another recommendation is to load all data to a staging table instead of the production tables. Then if the job fails, you can do an On Failure task to truncate that table. This extra table then requires a final step of loading the data into the live tables.


PS: A little info from BOL on DTS and transactions:

"DTS and Distributed Transactions
DTS packages provide support for distributed transactions for those providers that support DTC. The DTC service must be running for package and step transactions to work. In addition, each provider in the transaction must support DTC.

The package transaction properties are controlled on the Advanced tab of the package properties.

By default, each step in a package handles it's own transactions and does not coordinate it's transactions through DTC. To enlist a task in a DTC transaction you must set the "join transaction if present" check box in workflow properties of the task. Other DTC transaction options for a task are to "Commit transaction on successful completion of this step" and "Rollback transaction on failure.""

Best Regards,
Joseph Sack,

http://www.JoeSack.com

Author of "SQL Server 2000 Fast Answers for DBAs and Developers".