SSIS Error "Could not create a managed connection manager." in VS2022

Question

I just upgrade MSSQL and VS to 2022 and I have a data flow where the destination is an Always encrypted table in SQL Server. I switched the cllient provider to the Microsoft SQLClient Data provider, The connection test is successfull, the data preview works however when I try to run the package, I receive this error message "Error: 0xC0208452 at Load DW, ADO NET Destination [2]: ADO NET Destination has failed to acquire the connection {45360D12-CEA8-47A1-8D9B-F27660B235C6} with the following error message: "Could not create a managed connection manager.". What is missing?

Answer 1

I solved the issue by dropping the ADO destination and create a new one in the data flow, and set the table and mappings again. Also, this is the configuration of the properties I had to set in the connector to make it work with Always Encrypted:
The ADO destination for the encrypted table did not work with the .net Provider \ SqlClient Data Provider, only with the Microsoft SqlClient Data Provider.

Answer 2

Hi @Ricardo Silva,

Error message: "ADO NET Source has failed to acquire the connection '...'" with "Couldn't create a managed connection manager. "

The potential cause is that the ADO.NET provider used in the package isn't installed in the SSIS integration runtime. You can install the provider by using a custom setup. You can find more details about custom setup in Customize setup for the Azure-SSIS integration runtime.

See Troubleshoot package execution in the SSIS integration runtime

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

I was able to resolve the issue by using Microsoft OLE DB Driver for SQL Server option: