What are the most reasonable ways to move table data from SQL Server to Oracle (on *nix) on a regular basis?
SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command or use the Enterprise Manager interface.
Follow the steps below to link your Oracle to SQL Server right from the Object Explorer pane: On the Management Studio, choose your SQL Server Instance. Click on the Server Objects from the Object Explorer pane and select Linked Servers, then New Linked Server.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With