Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    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.

    Add comment
    10|10000 characters needed characters exceeded