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..
ssisblog 