CODEWITHSUNDEEP
oracle-databasessis
RWL01
RWL01

Reputation: 478

Attempting to connect to ORACLE with SSIS

I'm attempting to make a connection to ORACLE from within SSIS on my development server (WIN 2008 R2) using the "Microsoft Connector for Oracle by Attunity". I entered all the information but I get the below ORA-12154 error:

enter image description here

Updated Below is the log information associated with the above error.

Fatal NI connect error 12170.

VERSION INFORMATION:
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
  Time: 03-JUL-2012 09:32:34
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 60
    nt OS err code: 0
  Client address: <unknown>

Question What is the process to turn on tracing?

Updated When I do a TNS Ping from the dev server I get a TNS-12535 error:

enter image description here

However, when I connect using Oracle SQL Developer using what appears to be the same information it connects without an issue.

When I do a TNS Ping from my local machine (WIN 7) it successfully resolves.

Question

Does anybody have a suggestion on how I can get this to work in SSIS on my development server?

Upvotes: 1

Views: 2908

Answers (1)

m1yag1
m1yag1

Reputation: 829

I know this is 9 months old but perhaps my suggestion may help future searchers. The error you are experiencing could be caused by several factors:

  1. You do not have a tnsnames.ora or sqlnet.ora file in your /app//product//client_1/admin/network directory. You can generate one easily if you install your client with the development tools and run the net config program. Any errors in either of these two files will cause your connection to fail instantly. This can be a huge headache!

  2. You may need to add an environmental variable named TNS_ADMIN and the value above for the file location.

  3. You may need the file location of the Oracle client added to your PATH variable.

  4. You may need a string value registry entry called TNS_ADMIN in the root of HKEY_Local_Machine\Software\Oracle folder named TNS_ADMIN with the value above of the file location of your tnsnames.ora and sqlnet.ora.

I would first start with number one (most important) and move down the list. Save yourself the time and instead of simply getting the instant client from Oracle's website download the the full installer and install the instant client that way b/c it will place all the right entries in the registry and do your PATH location as well. Always restart after installing the client! I hope this helps.

Upvotes: 3

Related Questions