Fix SSIS Upsert Destination "Error Creating Managed Connection Manager" Due to Incorrect .NET Provider

SSIS PowerPack
,
1

Problem

When running an SSIS package that reads data from any source and writes to SQL Server using the Upsert Destination, the package fails during validation with the following error:

Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException:
Error creating managed connection manager.

at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(...)
...
Upsert Destination (Insert, Update, Delete) failed validation and returned error code 0x80131500.

Interestingly, the Source component works correctly, and the SQL Server connection may also test successfully. The failure only occurs when the Upsert Destination attempts to acquire the SQL connection during package execution.

Cause

The issue is caused by using an incompatible .NET Provider in the SQL Server Connection Manager.

The SQL Connection Manager was configured to use:

:cross_mark: Microsoft SqlClient Data Provider

Although the connection could be created and tested successfully, the Upsert Destination was unable to acquire the managed connection during validation, resulting in the DtsCouldNotCreateManagedConnectionException.

Solution

Change the SQL Server Connection Manager to use:

:white_check_mark: SqlClient Data Provider

After switching the provider, the Upsert Destination is able to acquire the SQL connection successfully, and the package executes without errors.

Steps

  1. Open your SSIS package.

  2. Edit the SQL Server Connection Manager used by the Upsert Destination.

  3. Locate the .NET Provider setting.

  4. Change the provider from:

    Microsoft SqlClient Data Provider

    to:

    SqlClient Data Provider

  5. Save the package and execute it again.

Result

After changing the provider:

  • Source component continues to retrieve data successfully.
  • Upsert Destination validates successfully.
  • Data is inserted/updated in SQL Server without errors.

Conclusion

If you encounter the following error while using ZappySys Upsert Destination:

DtsCouldNotCreateManagedConnectionException:
Error creating managed connection manager

And your source component is functioning correctly; verify the .NET Provider configured in your SQL Server Connection Manager.

Switching from Microsoft SqlClient Data Provider to SqlClient Data Provider resolved the issue in this case.

Related Components

  • ZappySys Upsert Destination
  • SQL Server Connection Manager
  • SSIS
  • SQL Server
2

Thanks for share information