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.
495 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 56,531 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.

  2. AniyaTang-MSFT 12,316 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://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

    0 comments
  3. Olaf Helper 40,896 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
  4. Duncan Dubick 0 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.