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