transaction dts

[fmardani]

Hi,
DTS has a transaction property whcih I believe can be ticked so that if one of the steps in the DTS fails then all the previous steps get rolledback.
Is this true? It's just that the DTS on my system does not seem to be rolling back
Are there any other settings that I should be looking int?
Thanks

 

[john76]

I haven't actually set it up on my system yet, but here's a summary from Microsoft SQL Server 2000 DTS by Timothy Peterson.

"You can configure a step's participation in a transaction on the Options tab of the Workflow Properties dialog."

"Data connections and tasks have to be able to participate in distributed transactions, or else they are not allowed to join DTS Transactions."

List of tasks that CAN participate:
1. Transform Data
2. Data Driven Query
3. Execute SQL
4. Bulk Insert
5. Message Queue
6. Execute Package
The other tasks are not allowed to participate.

Also, if your connection is not to SQL Server, it must be either a) an OLE DB provider that implements ITransactionJoin or b) an ODBC driver that supports SQL_ATT_ENLIST_IN_DTC

Now, assuming you are "safe" with these restrictions, to set up the individual steps that will participate, right click on the icon in the design window and select Workflow -> Workflow properties. The "Options" tab will have a few checkboxes:
1) Join Transaction if Present - You will need to check this box for EACH step that will be participating in the transaction
2) Commit Transaction on Successful Completion of This Step - only check this box if you are ready to commit the ENTIRE transaction
3) Rollback Transaction on Failure - Basically, if this box is checked and this step fails, the entire transaction is rolled back to the last COMMIT

There's more to it, but hopefully this at least gets you started. Like I said, I haven't actually done this for myself yet so let me know how things work out!

John