Post reply

How To rollback work in DDQ

  • 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

  • March 23, 2004 at 5:15 am

    #500000

    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.

  • March 23, 2004 at 6:32 am

    #500015

    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

  • March 24, 2004 at 8:18 am

    #500230

    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

  • March 24, 2004 at 8:29 am

    #500236

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

  • March 24, 2004 at 10:43 pm

    #500360

    Hi Julie,

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

    Ramesh

  • March 31, 2004 at 6:01 am

    #501257

    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

     

  • March 31, 2004 at 7:44 am

    #501279

    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  

  • April 5, 2004 at 7:17 am

    #501739

    Thanks julie my problem got solved.

    Ramesh

     

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

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