VS 2017: Integrated services on SQL Server 2019

Question

I have a SSIS (SQL Server Integrated Services) project in Visual Studio 2017 and I am migrating it to our new SQL Server 2019 servers.

But I have an issue as some of my packages requires parts of it to run in transaction mode (TransactionOption = Required).

Unfortunately when I enable the TransactionOption = Requried I get the following error:
[SRC EDW Stage Dimension [21]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "EDW" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: SRC EDW Stage Dimension failed the pre-execute phase and returned error code 0xC020801C.

Whenever I switch back to my old servers again, the package runs as expected. And if I set the TransactionOption to Supported, the package also works on the new server (but other stuff will fail if new rows are added, which is why I need the TransactionOption to be Required).

So what am I missing on my new SQL Server? Is it a driver problem or do I need to flip a bit somewhere?

Answer 1

Hi @Ole Frederiksen ,

Have you check if MSDTC is started on the machine for SQL Sever 2019?

Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.

Click to expand Component Services and click to expand Computers.

Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties.

Click the Security tab of the Local DTC Properties dialog.

In that dialog box, enable "Network DTC Access" and also "Allow Inbound" and "Allow Outbound".

Here is a similar issue you may take a reference:

transactionoption-in-ssis

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.