One of the nice things about the Microsoft Connector for Oracle is that it doesn’t require installation of an Oracle client. But because of this, you may not have the expected settings and files on the computer where your SSIS package is running.
A client ran into this recently, and the answer was to create a system environment variable.
Although you can now specify an Oracle database using the EzConnect format, it’s still very common to use a tnsnames.ora file. This file specifies a connection name and connection details for an Oracle database. It’s helpful in that you can provide a “friendly name” to the database specified in the file and then reference the file from multiple client tools.
When using a tnsnames.ora file, the connector needs to know the location of the folder containing that file. This location can be specified by a system environment variable or a registry key.
If you have installed an Oracle client, the tnsnames.ora file will likely be located in the ORACLE_HOME\network\admin folder and you will already have the required registry key and/or environment variable.
If you have not installed an Oracle client, you will likely need to add an environment variable on the computer running SSIS.
To add the environment variable in Windows 10, Windows 11 or Windows Server 2022:
- Right-click the Start icon and select System.
- In the Settings window, select Advanced System Settings.
- On the Advanced tab of the System Properties window, select Environment Variables.
- In the Environment Variables window under System, select New.
- In the New System Variable window, enter “TNS_ADMIN” for the Variable name and the correct path to the folder that contains your tnsnames.ora file for the Variable value.
- Select OK in the New System Variable, Environment Variables, and System Properties windows.
Vague Error Message
If the Oracle connector cannot find the tnsnames.ora file, it doesn’t return a detailed error message to tell you this. Instead, you get the generic “DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER” telling you the AcquireConnection method call to the connection manager failed. Basically, it assumes that you are supplying the connection info in EzConnect format and then can’t find the database at the friendly name you provided in the connection manager. Microsoft Support helped us determine that this was the cause of our connectivity issues after we logged a support request.
I hope this helps someone avoid time spent troubleshooting. If you have more tips about the Microsoft Connector for Oracle or tnsnames.ora files, feel free to leave them in the comments.
