Solved

How to copy data from sql server to oracle

Posted on 2013-05-14
6
569 Views
Last Modified: 2016-02-10
Hi

The problem is we need to copy data routinely ie set up automation to copy data from a sql server database to an oracle.

What would you recommend?

I looked into doing it with an SSIS package but we have server 2012 and needs to interact with the oracle client to work. Is there a way of firing the data strait into the oracle database without client software etc necessary on oracle server? Thanks
0
Comment
Question by:jazz__man
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39165232
Except for the JDBC Thin client connection (and some 3rd party connection apps), to connect to an Oracle database, you need the Oracle client installed.

You might look at pulling the data instead of pushing the data.

You can set up Heterogeneous Services on the Oracle side to pull the data from SQL Server.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_concepts001.htm#ADMIN12078
0
 
LVL 1

Expert Comment

by:enaliev
ID: 39165262
Hi,

as an alternative, you can use some ETL solution for this task.
E.g., you can use Talend Open Studio to create a package (application) and run it on demand.
0
 
LVL 15

Accepted Solution

by:
Walter Ritzel earned 500 total points
ID: 39167726
If you want to transfer data between the 2 databases, but dont want to change servers configuration by installing any new type of software, I believe your best alternative would be an ETL Tool, like the one suggested above. The other point is: do you know if your SQL Server supports Oracle linked servers (http://support.microsoft.com/kb/280106?wa=wsignin1.0)
If yes, a simple creation of a linked server on sql server side would allow you to create a t-sql procedure to insert whatever information you want on oracle.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39167772
wpcortes,

Step 1 in the link you posted:  
You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.
0
 
LVL 15

Expert Comment

by:Walter Ritzel
ID: 39168005
Thanks, Slightwv, that was exactly what I was trying to get the author to answer. Because his approach was using SSIS, but was unconfortable with the idea of adding new software. But if the ability to create linked servers to oracle, then this should be no concern.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39168203
OH, They may already have the Oracle client installed.  I get it know.  Sorry.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now