Error while setting up a connection with Oracle Connection Manager in SSIS

1
votes

I have published a SSIS package with Oracle Connection Manager on two different servers (test and production) running SQL Server 2019. The configurations of these two servers is the same. On the test instance it runs without any problems. Nevertheless, on the production server I get the following errors when I run the package:

"DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER". Error when calling the AcquireConnection method from the connection manager "Oracle" (error code: 0xC0014009). Error messages may have already been published that contain more information about the error when calling the AcquireConnection method.

Error while setting up an ODBC (Open Database Connectivity) connection to the database server.

I have checked the installed ODBC drivers, but can't see any difference:

Here the installed drivers on the test server:

Installed drivers on test server

And these are the installed drivers on the production server:

Installed drivers on production server

All these drivers are installed in 64bit variant. The packages are also running in 64bit.

In addition, I also checked the tnsNames.ora file, which is also identical on both machines.

Maybe someone here can give me a decisive tip. Many thanks in advance.

UPDATE: Testing the Oracle ODBC Driver Configuration on the production server is successful. But doesn't solve my problem.

Driver Configuration Test

1
sql-serveroraclessis
Are you using a didcated user or integrated security? In first case it might be that the user you have on the productive server is missing privileges. Furthermore it might be related to firewall settings or similar. – Tyron78
I am using a dedicated user. When i test the connection in the configuration menu it is successful. – ReFab

1 Answers

1
votes

I was able to solve the problem myself. The TNS Admin environment variable, which should point to the directory with the tnsNames.ora file, contained a wrong directory.
I specified the correct directory and now the connection works.