Update: I have posted a revised guide for connecting with SSIS using newer components.
Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you’ll have to install some software on your server to make things work. I’ve put together a short guide on connecting to Oracle from SQL Server. The process doesn’t take very long but there are several hoops to jump through.
- Install Oracle Database 10g Client Release 2
- Install using the InstantClient option
- Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
- Select the Oracle Data Access Components option (not .NET!)
- Edit TNSNAMES.ora
- <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
- There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):
DMDEV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = FRIENDLYNAME) ) )
There are a couple of things you need to change:
- HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
- SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
- Configure provider in SQL Server
- Create a linked server to the Oracle Database
- Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
- Provider: Oracle Provider for OLE DB
- Product Name: “Oracle” is fine here
- Data Source: This should match the HOST you defined in TNSNAMES.ora
- Select Be made using this security context and supply the remote login and password
- Query the linked server:
SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP