A bit of a conundrum here; I'm trying to survey what are my best to worst-case options.

For a given SSIS package, I have a Data Flow Task which connects via OLE DB for both the Source and Destination servers. Source and Destination are not only different connections but they are different SQL Server instances/machines. I set TransactionOption to Required at the package level and Support at the Task level, respectively.

As such, DTC is triggered and the package "hangs" at the task when executed. I see the transaction via DBCC OPENTRAN() is calling "DTCXact" and trying to read the metadata of the source server's resource from which I'm trying to read data. I have to kill the transaction or stop the package execution to clear it.

I've enabled DTC on the Destination server. The problem is I don't believe the Source server has DTC properly enabled and I have no control over the Source server and, politically, I may not be able to convince the other team to enable DTC on the Source server. I've tried adding their Source server as a linked server but the same issue arises with an error message of "The partner transaction manager has disabled its support for remote/network transactions".

Firewall is not an issue, I don't think. With transactions disabled in the package, the package executes successfully. The source and destination servers both use the standard SQL Server port. No connection issues at all; only with transactions enabled, of course.

To add insult to injury, the source server is using SQL 2019+ and the destination server is using SQL 2014. Right there, even if DTC was enabled on both servers, there may be some additional complexity and limitations involved.

The whole idea is to ensure data consistency, integrity, and no data loss even if the connection is interrupted during SSIS package execution.

With all that said, what are my other options here to get the benefits of transactions but without having the ability to utilize DTC? Would Replication work or would I run into a similar issue with DTC? I have a feeling I might.

Can I run a transaction separately for the source and another for the destination? If so, would that involve using a different type of Task in SSIS? I was thinking maybe I can wrap a given task with an explicit transaction block: I'd call BEGIN TRANS before the task and then another script runs after the task has completed, checking if there are any errors and I either commit or rollback.

I'm trying to think of a keep-it-simple-solution. If I have have to abandon transactions because of DTC, that's fine, but I need another solution or two that could work.

Any suggestions or advice would be most appreciated. Thank you for your help in advance!

  • try to load the data in a staging area in destination ->destination_table_stg, then create an sql task in which runs on destination in a transaction to load the data to main table. so that the final data load is inside the transaction so that data consistent in the final table.
    – Biju jose
    Mar 8 at 10:10


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.