September 13, 2001 at 6:50 am
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
April 5, 2017 at 8:23 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply