CODEWITHSUNDEEP
oracle-databasessisdatabase-connection
Antonio Craveiro
Antonio Craveiro

Reputation: 121

Connect SSIS in local machine to a remote server oracle database

I have an ssis project in visual studio 2012 in my machine. I want to setup a connection manager to an oracle database that's in a remote server.

I've tried installing oracle and tried several types of connections managers. But I wasn't able to successfully test the connection in any of them.

What type of connection manager should I use and what software do I need to install in my machine?

Upvotes: 0

Views: 1738

Answers (3)

Chitt Ranjan Mahto
Chitt Ranjan Mahto

Reputation: 51

  1. HOST = OACLE_SERVER_HOST_ID (i.e 10.92.256.20)
  2. SID = Oracle SID (i.e ORA)

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_server_ip_address)(PORT=1521))(CONNECT_DATA=(SID=oracle_sid)))

Upvotes: 2

Antonio Craveiro
Antonio Craveiro

Reputation: 121

  1. Install oracle client;
  2. Install Oracle Data Access Components
  3. Add a tnsnames.ora to %installationFolder$\product\11.2.0\client_2\Network\Admin
  4. Write in file:

ServerName=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=)

(PORT= )

)

(CONNECT_DATA=

(SERVICE_NAME= )

)

)

  1. Open ODBC Data Source Administrator from Administrative tools (choose 32 or 64 depending on your oracle server)
  2. Add a system DNS: a. Choose a Data Source Name to identify your data. b. Fill the TNS service name according to your server c. Fill the User ID that has database access rights
  3. Open your SSIS solution and create a new connection manager of type ODBC,
  4. Make a new connection and in your system data source name there should be the option that you just added in the Data Source Administrator.
  5. Use the same user and the respective password to connect

Now you should have a connection.

Upvotes: 1

user3662215
user3662215

Reputation: 526

There are several ways to set up a connection to Oracle from SQL Server. The easiest approach is to set up a linked server object. Try the following links that show clear steps for creating the connection.

Linked Server

TNS, LDAP, Other

Hope one of these help.

Upvotes: 0

Related Questions