Problem
Sometimes, While configuring an ADO.NET connection in SSIS Upsert Destination to connect to SQL Server, you might occasionally encounter the following error during the ‘Test Connection’ step.
Test connection failed because of an error in initializing provider. Keyword not supported: ‘ProviderName’.
Cause
A common cause of this error in SSIS is using an older version of Visual Studio 2022 (earlier than version 17.12) with a newer version of SQL Server Integration Services Projects 2022 (Version 1.5). It is recommended to update Visual Studio to ensure compatibility with the latest SSIS features in recent releases.
How to check Visual Studio and SSIS Designer version
To check your Visual Studio and SSIS Designer version within Visual Studio, open Visual Studio and go to Help > About Microsoft Visual Studio, where the version number will be shown.
Go to Help > About Microsoft Visual Studio
From Visual Studio Extensions:
Open Visual Studio, Go to Extensions > Manage Extensions, Find the “SSIS” extension, and View the version details on the right side. for more details refer to below article:
Resolution
To resolve this error, you have two options
-
Option#1: Upgrade Visual Studio to VS2022 17.12 Preview 2 or higher:
- This approach ensures compatibility between your development environment and the latest version of SSIS.
- It’s a reliable solution if you’re comfortable with using a preview version of Visual Studio or wait for the public release.
-
Option#2: Downgrade SSIS to Version 1.3.2:
- This option involves reverting to an older version of SSIS that is known to work with older Visual Studio versions.
- However, downgrading might introduce compatibility issues with other components or features in your project.
Video Tutorial
SSIS Upsert Destination- Perform Bulk Upsert/Sync data in SQL Server,PostgreSQL and Amazon Redshift
Conclusion
The best solution for you will depend on your specific project requirements, development environment, and comfort level with using different versions of Visual Studio and SSIS. By carefully considering these factors and following the recommended steps, you should be able to resolve the “Keyword not supported: ‘ProviderName’” error and successfully execute your SSIS package.