SSIS Can't connect to Oracle
I have a 32-Vista machine. I am using SQL Server 2008 R2. I have created a SSIS project. Under the Connection Manager, I am trying to connect to a remote Oracle 9 server Database(in the same office). The TNSNAMES.ORA is in a different server folder \\fg6ler\SDD-TNSadmin and I am pointing to it in the Registry. Do I need to have a copy of it in my local machine Or has it to be only in the Oracle server? The file TNSNAMES.ORA has: ora1.cpd.ca,ora1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tfsoradbat01.cpd.ca)(PORT = 1529)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora1.cpd.ca) ) ) Also the Oracle DBA gave me these info: Schema_name: PHIT_TEST Passwd: tester Database_name: ora1 Server: tfsoradbat01 Port: 1529 Objects_owner: ORAPHA I tried with Oracle Provider OLEDB from SSIS and have the following error. Do I have to load anything from the Oracle? Error: ORA-12154:TNS:could not resolve the connect identifier specified. I have in Connection Manager(SSIS): Provider=NAtive OLEDB\Oracle Provider for OLEDB Server or file name=ora1(also tried with tfsoradbat01 server name--same error) User name=PHIT_TEST Password=tester ----------- Under registry, I have: Computer-->HKEY_LOCAL_MACHINE-->SOFTWARE-->Oracle--> -KEY_OraClient11g_home1,ODP.NET Now KEY_OraClient11g_home1-->TNS_ADMIN , REG_SZ, \\fg6ler\SDD-TNSadmin What am I doing wrong.Thanks in advance. I also tried: Server Or file name=ora1.cpd.statcan.ca,ora1 (Just to match the TNSNAMES.ORA)--Same error Also Tried Microsft OLE DB porvider for Oracle in SSIS Server name=ora1,user name=PHIT_TEST,Password=tester--Same error
September 28th, 2011 11:09am

Hi, to use the Oracle Provider, install the Oracle Client Tools on your workstation. After that you have to configure the tnsnames.ora on your workstation. Cheers, Tillmann
Free Windows Admin Tool Kit Click here and download it now
September 28th, 2011 11:18am

How I will do that? where Will I het it and exactly which one should I load?
September 28th, 2011 11:20am

Normally the client tools are on the Oracle Installation CD or you can download them from Oracle (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html). The client tools are part of the server installation. Ask your admin which client tools are compatible with Oracle 9. After you have installed the client tools, you can configure the tnsnames.ora through Oracles Net Manager. Cheers, Tillmann
Free Windows Admin Tool Kit Click here and download it now
September 28th, 2011 11:37am

If y r still there, I checked that I have Oracle Client installed on my machine. I did:Start-->All Programs-->Oracle-OraClient11g_home1-->Application Development I have: SQL Developer and SQL Plus
September 28th, 2011 1:54pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics