Parallelism in ETL Process :: SSIS 2008 and SSIS 2012

Posted on December 21, 2011 by akhil393

Hi, Today we will talk about achieving parallelism in ETL using SSIS 2008 and 2012 We can generalize this topic two ways is by

1) Using maximum concurrent executable s in SSIS control flow and data flow( easy way to achieve parallelism and not so dynamic everythng should be configured in design)

2)Partitioning the source and destination ( little bit complicated but most power full way to achieve parallelism, everything is dynamic)

1) There is a setting in the SSIS which allows the user to specify the maximum number of executable’s it can execute in a given package

Max Concurrent executable

2)Partitioning the source and destination ( little bit complicated but most power full way to achieve parallelism, everything is dynamic)

In order achieve parallelism in ETL

1) Source and destination should be partitioned according the Data load

2) Entire work load should be divided to individual tasks

3) Each task should be sub divided into multiple tasks

4) Single unit of task should not not depend up on the other task(dead locks, I/O waits..)

5) before using the maximum threshold CPU for parallelism proper testing should be done, so there are always resources available for system processes Some times Parallelism may degrade the performance

6) If work load is divided in to to many small tasks which may also degrade the performance (ex: Time spent by the Worker thread to wait for child threads may exceed single execution of the process )