Share via

Issues in using Microsoft.Data.SqlClient in SSIS project

veera 0 Reputation points
2023-11-07T23:13:16.0566667+00:00

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."

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
542 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duncan Dubick 5 Reputation points
    2024-02-21T16:16:04.5733333+00:00

    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.

    1 person found this answer helpful.
  2. Bruce (SqlWork.com) 68,716 Reputation points
    2023-11-07T23:29:26.5233333+00:00

    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.

  3. AniyaTang-MSFT 12,446 Reputation points Microsoft Vendor
    2023-11-09T02:22:37.98+00:00

    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://zcusa.951200.xyz/en-us/sql/integration-services/connection-manager/ado-net-connection-manager?view=sql-server-ver16#managed-identities-for-azure-resources-authentication.

    Best regards,

    Aniya

    0 comments
  4. Olaf Helper 45,371 Reputation points
    2023-11-09T06:54:59.2266667+00:00

    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.

    0 comments
  5. Luis Martinez 0 Reputation points
    2024-10-08T15:47:17.0333333+00:00

    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

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.