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

Posted on 2011-11-01
Last Modified: 2013-11-30
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
Question by:GronkeyKong
    1 Comment
    LVL 1

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now