How To rollback work in DDQ

Question

How To rollback work in DDQ

rameshkondru

SSC Enthusiast

Points: 174
More actions
March 22, 2004 at 11:48 pm

#59197

Hi
I have ddq tasks in my DTS package if any one of these tasks fails i should roll back all the work done. Can any one suggest me a way to achieve it.
Thanks
Ramesh kondru

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Right click on any whitespace or goto Package and pcik Package Properties. On the Advanced tab there are Transactions and COmmit when package completes options for the package as a whole. See SQL BOL to make sure this meets your needs.

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 2

Hi Anthares,

Thanks for your reply, i tried it before writing to forums but it doesn't work out . i selected "Use transactions" and "commit on successful package completion". I do thought it will work but it didn't. if the error is at 4th ddq it should rollback first 3 ddq's transactions, unfortunatelyit is not happening.

Thanks

Ramesh

jliles SSC Enthusiast Points: 147 More actions · Answer 3

Ramesh,

In addition to what Antares mentioned, for each DDQ step which you want to roll back the whole transaction if it fails...

1. Right click the DDQ step and choose Workflow > Workflow Properties

2. Choose the Options tab.

3. In the Transaction section, check off "Join transaction if present" and "Rollback transaction on failure"

4. In the "Execution" section, check off "Fail package on step failure"

This is the configuration I use for my packages with this requirement, and it will roll back the actions of the package(assuming the whole package execution is wrapped in a transaction as Antares suggested) if any of the DDQ steps fail.

Hope that helps,

Julie

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Thank you Julie, I was missing something key to all this.

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 5

Hi Julie,

Thanks a lot, it really worked out. Once again thanks for your help.

Ramesh

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 6

Hi julie,

When i run package with all the above options i am getting an error that is msdtc service is not available, if i unselect join transaction if present, roll back is not taking place. could you pls help in this regard

Thanks

Ramesh

jliles SSC Enthusiast Points: 147 More actions · Answer 7

Ramesh,

Yes, the DTC (Distributed Transaction Coordinator) service needs to be running for transactions within DTS to work. On your SQL server box, go to the Windows Control Panel and double click on the "Administrative Tools" icon, then double click the "Services" icon (depending on your configuration it might be found directly in Control Panel).

Scan the list of services and find the entry for "Distributed Transaction Coordinator". If the status is not shown as started, you can start it by right clicking the entry and selecting "start" from the context menu. It's probably a good idea to go to the properties of this service and configure it to start automatically whenever the computer is restarted, especially if this package is part of an important business process in a production environment.

Julie

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 8

rameshkondru

SSC Enthusiast

Points: 174

April 5, 2004 at 7:17 am

#501739

Thanks julie my problem got solved.

Ramesh