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