Export large amount of data from Oracle 10G to SQL Server 2005

Question

Dear all,

I need to export 100 million data rows (avg row length ~ 100 bytes) from Oracle 10G database table into SQL server (over WAN/VLAN with 6MBits/sec capacity) on a regular basis. So far, these are the options that I have tried and a quick summary. Has anyone tried this before? Are there other better options? Which option would be the best in terms of performance and reliability? The time taken has been calculated using tests on smaller amounts of data and then extrapolating it to estimate the time required.

1. Using data import wizard on the SQL server or SSIS packages to import the data. It will take around 150 hours to complete the task.
2. Using Oracle batch job to spool data into a comma-delimited flat-file. Then using SSIS package to FTP this file to the SQL server and then load directly from the flat-file. The issue here is the size of the flat-file which is expected to run in GBs.
3. Although this option is drastically different, I am even considering the option of using Linked Server to query the Oracle data directly at run-time to avoid bringing in data. Performance is a big problem and I have limited control over the Oracle database in terms of creating table indexes.

Regards,

Uniball

Answer 1

I load 50 GB files all the time..this is what I do. If I have a linked server I use the SSIS to load the table, just make sure that you select FAST LOAD and Table Lock, make sute Table Lock is checked otherwise it won't be a truly minimally logged operation, your DB also has to be in Simpleor Bulk recovery model

see image

If I can I prefer to bcp out and then BCP in or BULK INSERT

Answer 2

Think about breaking the process into chunks so you can get a bunch of things done in parallel. For example, if the time to export 100,000 records from Oracle to a flat file is equal to the time to copy the file over, which is equal to the time to import the file into SQL then you can conceivably build a system that does all of these concurrently, like an assembly line. This way, too, if one piece fails you don't have to start over. Obviously this will take some experimentation. You don't define "regular basis" which might dictate the design somewhat, with system load being just one of the trade-offs.

Cheers, Brian