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).
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.