x

DTS workflow precedence from On Success to On Completion

Here I would like to know one thing. While setting DTS package properties, I had enabled Use Transactions option in DTS package properties - Advanced Tab. So that transaction will be rolled back whenever DTS get fails.

Transaction Isolation Level is Read Committed These both options are as default behavior. However when I run DTS, one of my step (whom I have intentionally kept to fail) fails, later my DTS keeps its execution as I had On Completion workflow precedence. When I verified the destination table, I found they had records transferred successfully.

Can you please suggest me, what the exactly use of keeping the Use Transactions option enable? Why didn’t me transactions get rolled back? Question sounds bit silly, but I am bit new in DTS programming.

Thanks in advance,

Mahesh

more ▼

asked Oct 21, 2009 at 03:39 AM in Default

Mahesh Bote gravatar image

Mahesh Bote
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Here's a link to an MSDN article that should help: http://msdn.microsoft.com/en-us/library/aa933474(SQL.80).aspx

Summary:

  • Set the Use Transaction option for the package
  • Check Commit on successful package completion
  • (critical step) - Check "Join transaction if present" for the step in question

I hope this helps.

more ▼

answered Oct 21, 2009 at 04:20 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476

asked: Oct 21, 2009 at 03:39 AM

Seen: 2427 times

Last Updated: Oct 21, 2009 at 03:39 AM