Search code examples
oracle11gssisoracleclient

ORA-12541: TNS: no listener in SSIS

We have oracle oledb connections in SSIS packages that are working well on windows server 2008.

We moved them to windows server 2012 and installed the needed softwares. We installed oracle client (oraoledb driver), moved tnsnames.ora, ldap.ora and sqlnet.ora to %Oracle_Home%\Network\admin path, add %Oracle_Home% and %Oracle_Home%\bin to path variable.

But on server 2012 oracle connections are giving this error ORA-12541: TNS: no listener. Where as on server 2008 same oracle connections are working fine.

Looked so much across internet but found these solutions:

  1. Check tnsnames.ora
  2. Check listener is running
  3. Check path variable contain oracle home, oracle_home\bin

I don't see a problem with tnsnames.ora because same file is present on both window servers. Correct path variables are also set. Listener is also running (since SSIS on server 2008 is connecting). And I am able to ping oracle db server from both machines.

Can anyone suggest anything that we may try.

Solution

  • To put a formal answer in here.

    Basic troubleshooting steps with SSIS:

    1. Use the database native tools to check connectivity
      • In this case for Oracle that is SQLPLUS.EXE
      • If you have an issue with native tools then it isn't an SSIS issue
    2. Check that you can resolve the host by using PING <hostname>.
      • If that doesn't work try PING <ip address>
    3. If ping works, check the port with TELNET <host> <port>
      • If that doesn't work, either the service is not listening or you need to get your network guys to open the port

    This goes for any network service

    i.e.

    • SQL Server (default port 1433)
    • a web server (default port 80 for unencrypted comms)