MicrosoftHi,
I have VS solution, which uses the Oracle Connection Manager.The solution brings data from Oracle and dumps to a SQL Server 2019 Edition.It works fine on my laptop. When the project is deployed to the catalog on a separate ETL server , we have parameterized the Connection String and Pwd.the connection string looks something like this:-
SERVER=TnsName;USERNAME=UName;WINAUTH=0;
and another for a password.
The ETL server has the Oracle Connection Manager along with the Oracle Client installed. When I schedule this to run on my ETL server I get these errors
There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "CompName" failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
For the server part in the connection, I have already tried with the name in the Tnsnames.ora file and also the actual oracle server name too. Both are not working.
My Catalog server is running
SQL Server 2019 CU18 Dev Edition.
Integration Services 15.0
Oracle Client 19c 64bit.
I am able to connect to the oracle server from the ETL server so its not a firewall issue.Can someone please advice what i am doing wrong here ?
Thanks.
