woensdag 17 november 2010

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.

image002
The SSIS Package will run from SQL01.
The SSIS Package contains a single Sequence Container:
image003
The package level Transaction configuration is set to:
image005
The Sequence Container Transaction configuration is set to:
image007
The SQL tasks Transaction configuration is set to: (all SQL Tasks)
image009

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

image011

The table on SQL02 is still empty:
image013

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:
image015

Let's run the package and look at the result.
We do get an error immediately:
image017
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
    image019
  5. Right click the Local DTC and select properties
  6. Select the security tab and set configuration as belowimage021
    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.
image023
After about 2,5 minutes the following is displayed.
image025
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.
image027
image029

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.
image031
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
image033
SQL Insert Bert Remote Task result on server SQL02
image035

Until now it is as we should aspect. Both tasks completed successful and data is inserted in the tables. The next step should fail!
image037
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!
image011
On SQL02 the table is empty, again.
image013
We are now ready to develop packages with multi server transactions!

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

6 opmerkingen:

Hennie zei

Ziet er goed uit.

F.L. zei

Thx for documenting !

Mojahid Rahman zei

This is an excellent document I was exactly looking for. God bless you for such a wonderful documentation. Thanks in deed...

kranthi zei

Thank you very much. Your document helped me.

Nigel Johnson zei

Thanks I have been struggling with this for a while

Marc Waanders zei

Op twee w2012R2 machines alles ingesteld incl. firewall maar nog steeds de melding:

[Connection manager "BSP.bsp"] 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".

Wat zou dat kunnen zijn? Is er misschien een restart vereist na instelling?

Marc