Issues in using Microsoft.Data.SqlClient in SSIS project

Question

I have installed visual studio 2022 image and installed SQL Server Integration Services Projects 2022. I am trying to implement functionality of ado.net using Microsoft.Data.SqlClient and running into below issues.

ADO NET Destination has failed to acquire the connection {C3BC4CC4-B2A4-46AC-97EC-F9080D6BF00B} with the following error message: "Could not create a managed connection manager.".

Exception received while enumerating ADO.Net providers. The invariant was "Microsoft.SqlServerCe.Client.4.0". The exception message is: "Failed to find or load the registered .Net Framework Data Provider."

Answer 1

I just had to deal with this and the frustration of these answers saying 'don't use CE' when it's clearly stated that we're trying to use the Microsoft.Data.SqlClient provider. The problem for me was the CE provider being included in my devenv.exe.config (in C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE) even when the provider was not installed.
Removing the "Microsoft SQL Server Compact 4.0 Client Data Provider" from the <DbProviderFactories> section fixed this issue for me.

Answer 2

you are trying to use SqlServer Compact driver, but have not installed.

https://www.microsoft.com/en-us/download/details.aspx?id=30709

It is more typical to use SQL Express.

Answer 3

Hi @veera

For information on configuring managed identities for Azure resource authentication in ADO.NET Connection Manager, you can refer to this section of the documentation: https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/ado-net-connection-manager?view=sql-server-ver16#managed-identities-for-azure-resources-authentication.

Best regards,

Aniya

Answer 4

working for bulk copying

The SSIS standard task/data flows works with BULK opertions, there is no need to implement anything this way on your own.

Use the SSIS standard tasks.

Answer 5

Hello Duncan,

Thanks for sharing the solution. It worked for me too. This is an issue introduced if you deploy all latest versions of SQL Server, VS2022 and integration services. See here:

https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices&ssr=false#overview

The work around proposed in the link above did not work and instead messed even more my dev environment. Your solution was simple and works