1

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.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
Pritesh
  • 1,938
  • 7
  • 32
  • 46
  • tried lsnrctl... got below error "Failed to open service , error 1060." – Pritesh Dec 13 '12 at 14:45
  • 1
    What do you have under Package Configurations? Is your production server changing some values based on a different XML file or similar? – milivojeviCH Dec 13 '12 at 14:49
  • Try also to run the `dtexec` command with `/REPORTING V` option to get verbose output of the package execution. – milivojeviCH Dec 13 '12 at 14:49
  • 1
    Since the package works locally but not on the server, what are the differences between the two environments? Do they both have the same Oracle client and `tnsnames.ora` files? Does `tnsping Server_Name` work on the server? Is there any firewall between the server and the Oracle server? – Pondlife Dec 13 '12 at 15:35
  • @Pondlife on my machine (where connection works) I tried tnsping Server_Name and got below results. Used parameter files: c:\apps\oracle\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias when tried same on ssis server I got below Used parameter files: C:\local\Oracle\clients\112_32\network\admin\sqlnet.ora Used HOSTNAME adapter to resolve the alias here in the end i got our old error TNS-12541: TNS:no listener – Pritesh Dec 14 '12 at 07:14
  • @mceda .dtsconfig are a bit different but oracle connection string is same... – Pritesh Dec 14 '12 at 07:32
  • @Pondlife My machine is Windows 7 64 bit and prod is windows server 2008 64 bit – Pritesh Dec 14 '12 at 07:36
  • If I understand your comment correctly, your Oracle client configuration is different on your workstation and on the SSIS server (because `$ORACLE_HOME` is different)? If so, you need to work out why they are different and make them the same; I don't know enough about Oracle to know what the problem is, but looking at `sqlnet.ora` and `tnsnames.ora` should give you some clues. – Pondlife Dec 14 '12 at 12:14

2 Answers2

2

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!

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • I notice bit difference regarding tnsnames on both machine… On My machine tnsnames.oRA is at C:\Apps\oracle\Clients\112_32\network\admin\sample and tnsnames.ora is at C:\Apps\oracle\network\admin On SSIS Server tnsnames.oRA is in below location C:\local\Oracle\clients\112_32\NETWORK\ADMIN\SAMPLE Does this hint anything regarding my problem? – Pritesh Dec 14 '12 at 10:00
  • Try copying your tnsnames.ora file to the server's C:\local\Oracle\clients\112_32\NETWORK\ADMIN directory – Mike Honey Dec 15 '12 at 00:17
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.

Yas V
  • 96
  • 2