Skip to Content

Extracting large amount of data from Oracle to SQL Table using Data Services.

Oct 12, 2017 at 11:45 AM


avatar image
Former Member

I am looking for some guidance with regards to extracting about 16 Billion rows from an Oracle DB. Some lookup transformation has to be applied to this data before writing it into a SQL Server 2016 staging DB. The objective is migration of this data, not building a DWH or it being part of an integration job that has longevity to it. We have been given a script to extract the data from the Oracle DWH. I am well aware that using the SQL Transform is not the best way to go about it, but time constraints on this project doesn't lend itself to pulling the script apart to develop into multitude of workflows, dataflows and mapping queries.

I am looking for advise, from consultants whom has dealt with large extractions of this kind in the most efficient way of doing it. The objective being for the extract job to run as quickly as possible.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Dirk Venken
Oct 13, 2017 at 08:07 AM

Where does the lookup data reside? If those lookups can be done fast (cache the lookup tables in memory), DS won't be the bottleneck in this process.

Speed up the process by:

  • reducing the data volume to be transferred :) - most probably you need all 16 billion rows, but do you need all columns in the target?
  • setting Array Fetch Size in SQL transform to 5000
  • increasing the Degree of Parallelism property of the dataflow
  • using SQL Server Bulk Load

In addition, you can also split up the input in a number of equally sized batches and process each batch in its own data flow. Run those data flows in parallel in order to make best use of the available system resources.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Dirk, great suggestions. Will apply these suggestions.