Configuring Transactions with Execute Package Task

• harishr

  SSC Veteran

  Points: 292

  More actions

  September 13, 2001 at 6:50 am

  #104514

  Hello

  Has any one had any experience in configuring transactions in SQL Server 2000

  with the Execute package task?

  I have serveral packages which encapsulate calls to stored

  procedures. Each package has its own connection to the

  same SQL Server.

  I use another package to chain all of these other packages

  together.

  What I would like to happen is if a package fails all the

  other packages roll back their work so nothing is

  committed.

  In the controlling package I have tried to set the

  workflow properties

  Join transaction = true

  Rollback transaction = true

  Fail package on step failure = true

  I have also set these in the individual packages

  themselves. But when I run it the individual packages

  commit even if one of them fails. Or I get a message

  saying that a step could not join the transaction ( MS DTC

  is running).

  So far the only way I can get the transaction to work is

  If I execute in main thread is set for each step. But

  this slows everything down.

  Does anyone have any suggestions.

  Thanks in advance

  Harish

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 5, 2017 at 8:23 am

  #1936806

  I have dealt with this in the past.  If you use the transaction handling in SSIS as you have tried, it requires that DTC be running and configured correctly.  The way I have handled this in the past, and I'm not saying that this is the best way, is to begin the controller package with an Execute SQL Task that issues a BEGIN TRANSACTION, and end the success with an Execute SQL Task that issues a COMMIT TRANSACTION, you need to check tran count and XACT_STATE().  Then in the Package OnError event handler have an Execute SQL Task with a ROLLBACK TRANSACTION, again checking transaction count and XACT_STATE().

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question