ORA-12541 error when deploying SSIS package to a server

Question

I am connecting SSIS to Oracle OLEDB connection using below connection string..

Data Source=Server_Name;User ID=U_Id;Password=Pwd;Provider=MSDAORA.1;

It works on my machine but when i run on SSIS server using C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec it gives me error: ORA-12541: tns: no listener

I tried playing around with file tnsnames.ora but did not get anywhere.

Answer 1

You need to identify which Oracle installed directory on the server has the Oracle 32-bit drivers installed. Oracle's Installer doesnt seem to offer any help for this - it's often quicker to uninstall the lot and start again.

The TNSNAMES.ORA you need to fix is in the NETWORK/ADMIN subdirectory below that Oracle 32-bit client installation.

I can't beleive it's 2012 (almost 2013) and we are still wrestling with this!

Answer 2

At work we had to install the 64 bit oracle and then the 32 bit oracle if the machine was a 64bit machine for it to work.

For me locally this link helped a lot to get tnsnames working correctly. http://www.dba-oracle.com/t_windows_tnsnames.ora_file_location.htm

Make sure the system variables has the path to tnsnames.ora otherwise apps don't appear to be able to connect.