Moving Data from SQL Server to Oracle Repeatedly

Question

What are the most reasonable ways to move table data from SQL Server to Oracle (on *nix) on a regular basis?

Answer 1

• Oracle Heterogeneous Connectivity / Database Gateways in conjunction with materialized view(s), PL/SQL, or Java
• SSIS or DTS: both can be scheduled but require more than read-only access to SQL Server
• Java (probably within Oracle but optionally at the OS) using ODBC or SQLJ to access SQL Server and, possibly, Oracle
• SQL Server scheduled to export to CSV, Oracle scheduled to import from CSV
• Any of the other ETL tools (e.g. Informatica, Cognos)
• Any of the myriad languages that can access both databases (but would require maintaining a third environment to run the application within)

Scheduling:

• Automatic / not reuqired with materialized views
• Oracle DBMS_JOB / DBMS_SCHEDULER
• OS-specific (cron, Windows Scheduled Tasks, etc)
• In the case of SSIS, DTS, or CSV export, scheduled within SQL Server

Answer 2

You can have SQL Server interface with Oracle directly through SSIS (or DTS for 2k). It will provide ETL functionality and can be scheduled on a regular basis.

Answer 3

Using SQL Server Integration Services (SSIS) is likely your best bet. If you're not familiar with SSIS, the best way to try something out is to use the SQL Server Export Wizard and have it create an SSIS package for you. For example, if you go into SQL Server Management Studio and right-click on your database, then select Tasks->Export Data. From there click next until you get to the "Choose a Destination" step. Select the "Microsoft OLE DB Provider for Oracle" and click Properties to define your database connection. When you click through the wizard, on the Save and Execute page, make sure you check the checkbox labelled "Save SSIS Package", on the next screen specify where to save the SSIS package. Once you finish the Export Wizard, your data will have been exported and you will have an SSIS package that you can use as is, or go in and tweak it to do more specific things. Once you have your SSIS package, you can schedule it by creating a SQL Server Agent Job.

Answer 4

I had success creating a linked server (from within the Enterprise Manager, I think) to Oracle on the SQL Server side. Then I could use normal stored procedures on both sides to accomplish smaller data movement and updates in both directions. This approach can bypass the need to try and put something together outside the databases.

Try hard to use the latest possible Oracle client on the SQL Server side though. I recall some defects in the 10.2.0.2 client and getting the 10.2.0.4 client required your "official" Oracle registration or purchase number or something.

For big data moves (or maybe even moves/updates you want done on a daily or less frequent basis), definitely use one of the ETL tools. We had Informatica for our ETL processes, but if SSIS can pull off what you need, that's fine too.

Answer 5

Create a database link from Oracle to Sql Server (heterogeneous connectivity). You can use this link to retrieve the data from Sql Server with a simple select statement. If you want to schedule you can use a materialized view or dbms_scheduler.

An alternative is to put your data in a csv file, you can use an external table or sqlloader to load this data in the Oracle database.