When using Microsoft’s Visual Studio (aka: Business Intelligence Development Studio) to create an SSIS package that has a connection to Oracle and the SSIS package deployment shows:

Error : AcquireConnection call to the connection manager . failed with error code 0xC0202009.

How can this be fixed?

Solution 1: Make sure the provider being used is installed on both the development and deployment machines. This may require Oracle client to be on the SQL Server machine. To see the providers that SQL Server has available, open MS SQL Server Managaement Studio, then connect to the Database Engine. See Server Objects, Linked Servers then Providers. To see the providers on a development PC with XP, right click and create a text file on the desktop. Rename the text file MyProviders.UDL. Double-click, or right-click Open, the text file and see the “Data Link Properties” form with the tab called providers. Test a connection, then close the form, then open the UDL file (using right-click open with) using Notepad. The connection string will be seen.

Solution 2: Use an XML configuration file to change the provider when deployed. To create a configuration file in Visual Studio, open the SSIS solution then right-click on the background of the Designer window and pick the Package Configuration Organizer. Make sure your configuration file is in a folder that SQL Server can see it, and that the file contains the connection string and password parameters. Change the connection string so that it has the correct provider. For Oracle it might be one of these:

Data Source=ORACLE1;User ID=ORACLE_USER;Provider=MSDAORA.1;Persist Security Info=True;

Data Source=ORACLE1;User ID=ORACLE_USER;Provider= OraOLEDB.Oracle.1;Persist Security Info=False;