Monthly Archives: October 2012

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:


Partially\ Semi 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..