VS 2017: Integrated services on SQL Server 2019

Ole Frederiksen 21 Reputation points
2021-12-06T15:50:04.767+00:00

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?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,414 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 30,566 Reputation points
    2021-12-07T05:41:18.667+00:00

    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.


0 additional answers

Sort by: Most helpful