SSIS Transformations – Blocking, Partially Blocking and Non blocking

The type of transformation that we choose to achieve particular task has a great impact on performance of SSIS. Hence we should have  clear understanding of different types of transformations available in SSIS and be careful enough while deciding among them.

Here, I am going to mention few points that help in better understanding of transformation:

Blocking

Partially\ Semi Blocking

Non-Blocking

Need to read all the inputs before they produce some output. Clearly, it consumes time Need not read all inputs Need not read all inputs
It creates a new buffer for the output It creates a new buffer for the output It uses the existing buffer. This introduces huge performance benefits
These transformations may create additional records i.e. the number of input records might be different from number of output records. Hence, they are asynchronous in nature. Number of output records may or may not match the number of input records. They are also asynchronous in nature Number of output records are same as number of input records. These transformations are synchronous in nature
New thread is  introduced in Data flow New thread might introduce in data flow No additional thread is created
Available resources are impacted heavily Less impact on available resources than Blocking transformations It has the minimum impact to available resources out of all the transformation types
Ex: Sort, Aggregate Ex: Merge, Merge Join Ex: Data Conversion, Derive Column, Look-up

 

Enjoy! Happy Coding..

Advertisements

One thought on “SSIS Transformations – Blocking, Partially Blocking and Non blocking

  1. Avik

    Nice briefing but if some more architectural details available for semi-blocking and blocking components it would have been great! As I’ve difficulty to understand why Lookup been fall into partial and Sort is blocking as New thread introduce in data flow is not also clear to me

    Reply

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