Parallelism in ETL Process :: SSIS 2008 and SSIS 2012

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 ) Declare variables  Conclusion: Parallelism can be save lot of time in ETL process if utilized properly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s