SSIS - Types of Transformations

SSIS transformation tasks can use memory or buffer in different ways. The way transformations use memory can impact the performance of your SSIS package dramatically. Transformations buffer usage can be categorised as below.

• Non blocking transformations
• Partially blocking transformations
• Full Blocking transformations

In general if you can avoid using fully blocking and partial blocking transformations, your SSIS package will perform better. The transfromations can be classified as below

Non blocking transformations

• Audit
• Cache Transform
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Devired Column
• Export Column
• Import Column
• Lookup
• Multicast
• OLE DB Command
• Percentage Sampling
• Script Component
• Slowly Changing Dimesion

Partial blocking transformations

•  Data Mining
• Merge
• Merge Join
• Pivot
• Unpivot
• Term Lookup

Fully Blocking Transformations

•  Aggregate
• Fuzzy grouping
• Fuzzy lookup
• Row Sampling
• Sort
• Term Extraction

If you clearly observe Sort is a fully blocking transformation, so its better to sort your data using the SQL command in OLE DB Source instead of using Sort transformation. Merge tranform requires Sort but not Union All, so use Union All wherever possible.

Hope this post helps in analysing the performance bottle necks in your SSIS package and also while developing new SSIS package for performance tuning.

Reference: DP (http://dptechnicalblog.blogspot.com/ )