How to copy data from sql server to oracle

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
LVL 2
jazz__manAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Walter RitzelConnect With a Mentor Senior Software EngineerCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
enalievCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
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
 
Walter RitzelSenior Software EngineerCommented:
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
 
slightwv (䄆 Netminder) Commented:
OH, They may already have the Oracle client installed.  I get it know.  Sorry.
0
All Courses

From novice to tech pro — start learning today.