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

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 2

Here is what I do: Connect to SQL Server by Oracle SQL developer using this link: https://kentgraziano.com/2013/01/14/tech-tip-connect-to-sql-server-using-oracle-sql-developer/

After you have added SQL jar, you will see SQL Server tab in the Connection window:

Then connect to the SQL Instance.

Then open SQL instance and choose database or table that you want to copy. Right click on any database/table, then click on "copy to oracle" there and choose the right user[database] at "Destination Connection Name " where you want to copy your tables.

You can also change some properties there. Click "OK" and that's it.

Let me know in case of any issues.