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

[former_member246919]

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.

[former_member187605 · Accepted solution]

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.

[former_member246919 · Accepted solution]

Thanks Dirk, great suggestions. Will apply these suggestions.