Enabling Distributed Transaction Support for SSIS

I need to build SSIS packages that handle transaction over multiple SQL Servers. When a SQL transaction fails in a sequence container all other SQL transactions in that container should be rolled back.

The SSIS Package will run from SQL01.

The SSIS Package contains a single Sequence Container:

The package level Transaction configuration is set to:

The Sequence Container Transaction configuration is set to:

The SQL tasks Transaction configuration is set to: (all SQL Tasks)

Both servers contain a test database with the table tblName in it.
CREATE TABLE [dbo].[tblName](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [nchar](10) NULL
) ON [PRIMARY]
GO

On SQL01 the below inserts are executed:
INSERT INTO [tblName]
([Name])
VALUES
('Arjan')
GO

INSERT INTO [tblName]
([Name])
VALUES
('Pieter')
GO

The table on SQL02 is still empty:

The SQL tasks in the package are executing the following commands:

• SQL Task: SQL Insert Bert Local
  INSERT INTO [tblName] ([Name]) VALUES ('Bert')
  GO
• SQL Task: SQL Insert Bert Remote
  INSERT INTO [tblName] ([Name]) VALUES ('Bert')
  GO
• SQL Task: SQL Insert Key 1 again local
  INSERT INTO [tblName]
  (ID,[Name]) VALUES (1,'Bert')
  GO

The first and second SQL task should be executed without errors, the last SQL task will cause a duplicate Key insert error and fail the sequence container.

I added a Pre-Execute brake point on the last SQL task to make it possible to check the results of the preceding SQL tasks.

Let's first run the package without Distributed Transaction enabled; DTC configuration is the same on both servers:

Let's run the package and look at the result.
We do get an error immediately:

When we take a look at the SSIS Package execution progress see multiple errors.

• On the SQL Insert Bert Remote task the below error is displayed:
  [Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection.
• On the Package level I see the below errors displayed
  [Connection manager "SQL02.dbAFTest"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".
  
  [Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D025.

The first error on the SQL task doesn’t clarify much. The other error mentions something about the distributed transaction. Because we configured the sequence container to REQUIRE the transaction it needs to be able to handle distributed transaction.

Let's configure DTC on SQL01 and SQL02.

1. Logon to the server with a local administrators account
2. Click start
3. In the search box type dcomcnfg and hit ENTER
4. Browse to the Local DTC
   
5. Right click the Local DTC and select properties
6. Select the security tab and set configuration as below
   I know that Mutual Authentication Required is more secure; this is however not supported on SQL Server Cluster.
7. Click OK
8. A warning will be displayed that a Reset of the DTC service is going to be executed. Click Yes
9. A message will be displayed that the reset is completed.
10. Do the same on the other server(s)

Let's run the package again and look at the result.

For a longer period then I would like to, the below status of the run is displayed, nothing seems to be happening. Get some coffee! And be patient.

After about 2,5 minutes the following is displayed.

Not where I hoped for so let's look at the progress errors.

• On the SQL Insert Bert Remote task the below error is displayed:
  [Execute SQL Task] Error: Failed to acquire connection "SQL02.dbAFTest". Connection may not be configured correctly or you may not have the right permissions on this connection.
• On the Package level I see the below errors displayed
  [Connection manager "SQL02.dbAFTest"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".
  
  [Connection manager "SQL02.dbAFTest"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00E.

Still seems to be wrong. When you compare these errors with the previous ones you will see some differences.

The first time the DTC error displayed: "The partner transaction manager has disabled its support for remote/network transactions."
This time the DTC error displays: "The transaction has already been implicitly or explicitly committed or aborted".

So what did we do wrong or even did forget? When you search the internet for the last DTC error you will find multiple references to the Windows firewall. So let's have a look. On both servers I found that the DTC TCP firewall Inbound/Outbound rules are still disabled.

Let's enable them, I hope you know how, do this on all servers that require to use SSIS and DTC.

With the firewall rules enabled, let's run the package again and look at the result.

This looks promising, before we continue processing let's look at the SQL results on the tables.

SQL Insert Bert Local Task result on server SQL01

SQL Insert Bert Remote Task result on server SQL02

Until now it is as we should aspect. Both tasks completed successful and data is inserted in the tables. The next step should fail!

And lucky us! It does!
Let's look at the progress errors.

• On the SQL Insert Key 1 again local task the below error is displayed:
  [Execute SQL Task] Error: Executing the query "INSERT INTO [tblName] (ID,[Name])..." failed with the following error: "Cannot insert explicit value for identity column in table 'tblName' when IDENTITY_INSERT is set to OFF.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is an expected error, but what we need to now are previous transactions rolled back?

On SQL01 the previous inserted Bert by the SQL Insert Bert Local task is gone!

On SQL02 the table is empty, again.

We are now ready to develop packages with multi server transactions!

See How to Cluster MSDTC for additional information how to configure MSDTC.