SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER for distributed transactions

▲
2
▼

I am trying to set dataflow tasks in a sequence container from SourceDb(exists on my local machine) to TargetDb(exists on azure & can be accessible from SSMS). I am getting following error whenever I run the package with transaction as Required.

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TargetDb" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

It works if I remove the transaction for that container. I have set all the distributed transaction settings correctly. I went through many link on stack overflow but nothing worked for me. Please suggest the solution.

I have also tried this process by restoring both db's on my local machine. It works with transaction successfully. There is nothing wrong with the package.

.net
sql-server
ssis
distributed-transactions
msdtc
asked on Stack Overflow Aug 9, 2017 by Pavvy

2 Answers

▲
2
▼

In order to use transaction in ssis you need to switch on Distributed Transaction coordinator. Another way is to use begin Transaction in execute sql task and in the connection properties set retainsameconnection = True in control flow and all the dft task that needs to be in transaction will follow after this and the last task will be another execute sql task with commit transaction. Follow the below link to use transaction without switchung on distributed transaction - https://www.mssqltips.com/sqlservertip/3072/sql-server-integration-services-ssis-transactions-without-msdtc/

answered on Stack Overflow Aug 9, 2017 by sam • edited Aug 9, 2017 by sam
▲
1
▼

Azure cannot participate in transactions with local SQL Servers AFAIK.

Limitations The following limitations currently apply to elastic database transactions in SQL DB:

  • Only transactions across databases in SQL DB are supported. Other X/Open XA resource providers and databases outside of SQL DB cannot participate in elastic database transactions. That means that elastic database transactions cannot stretch across on premises SQL Server and Azure SQL Databases. For distributed transactions on premises, continue to use MSDTC.
  • Only client-coordinated transactions from a .NET application are supported. Server-side support for T-SQL such as BEGIN DISTRIBUTED TRANSACTION is planned, but not yet available.
  • Transactions across WCF services are not supported. For example, you have a WCF service method that executes a transaction. Enclosing the call within a transaction scope will fail as a System.ServiceModel.ProtocolException.

See this documentation at Microsoft for a complete discussion of distributed transactions and Azure.

answered on Stack Overflow Aug 9, 2017 by Laughing Vergil

User contributions licensed under CC BY-SA 3.0