• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

SQL Server DTS: Job fails when 'Join transaction if present' is set to True for one of my Transformations. The mystery of Distributed Transactions.

How do I get 'Join transaction if present' to work on all my Transformations?

I have a DTS package that I want to be able to rollback all transactions if any step fails.  I am able to apply the 'Join transaction if present' property to many tasks and execute the package successfully on all but one (1) item, namely, Transform Data Task 12.Get AGRCPipeline (see circled in red from first screenshot).
 Snapshot of DTS Package in Design/Debug mode.

I have two (3) Microsoft OLE DB Provider for SQL Server connections and one (1) Microsoft Access connection.  
   CSI connection = Server 1 (SQL 2000) -- Database 1
   CSI (No Trans) = Server 1 with No transactions -- Database 1
   Deltek = Server 2 (SQL 2008) -- Database 2
   AGRCPipeline = Server 1 -- Database 3
   Accuate = Microsoft Access -- Database 4

The package executes successfully when 'Join transaction if present' is set to True for any of my Execute SQL Tasks as well as two (2) out of my three (3) Transform Data Tasks.  (see green check-marked from first screenshot)

I am executing via a SQL Server Agent job successfully until I include Transformation Task 12 in the distributed transaction.  
I've confirmed that the Distributed Transaction Coordinator (DTC) is running on the destination server using a BEGIN DISTRIBUTED TRANSACTION statement in SSMS.
The package is being executed from the server machine using SQL Server Agent as a scheduled job.

In my opinion there are too many tasks for me to impliment Fail workflows on ever task which is why I need the package to rollback if any one of the tasks fails.

BTW, the Transformation not cooperating is what I think DTC would consider an internal transation because the transformation is between two databases on the SAME server\instance.

Actually everything might be internal on this one because there is only one destination database.  Maybe I don't even need DTC.

 Fail on first error Commit on successful package completion Commit on successful package completion Join transaction if present
1 Solution
GronkeyKongAuthor Commented:
It appears that the ROLLBACK of all transactions after a FAILED package execution is actually working even though by Transformation #12 is not a "joined" transaction.  So I guess I don't need it.  In fact, I've read some things recently that seems to explain why...

"Note that because configuring a package step to join a transaction causes its associated connections
to be enlisted in the transaction, any other tasks that use those same connections will also be
enlisted in the transaction, even if they do not have the Join transaction if present option enabled."
Data Transformation Services. Guru's Guide to SQL Server Architecture and Internals by Ken Henderson, Page 536

I guess this means I literally only needed to set "Join transactions if present" to True for the first task enlisting my destination connection.  Maybe I'll get to test this later.  For now it is working.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now