Shalini's Knowledge Share

In SSIS, we have many different kinds of transformations that we can use to cleanse and restructure our data in the way we want before sending to our destination.

However, key thing to note is that behind our designer, all these transformations do not behave the same way. Each have their own way of performing, just like how our SQL queries would perform when using different keys.

In SSIS, in general, the components can be categorized into 2 types:

1. Synchronous – both the input and the output of a component uses the same buffer as the number of rows before and after is the same.

2. Asynchronous – The number of rows before maybe different to the number of rows after using the component. So the output uses a new buffer.

Keeping the above 2 in mind, the transformations are categorized into 3 types:

1. Non blocking (Row) Transformations – 

• These transformations are synchronous, meaning the buffers are being reused.
• Number of rows before transformations are equal to the number of rows after.
• Do not need to read the entire input to process – Faster
• No new threads are created
• Example: Lookup, derived columns, data conversions.

2. Semi-blocking transformations

• Asynchronous transformation, meaning the buffers are not being reused.
• Number of rows before transformations is usually not equal to the number of rows after.
• Do not need to read the entire input to process
• A new thread maybe created
• Example: Pivot, Merge, Merge Join, Unions All

3. Fully blocking transformations

• Asynchronous transformation, meaning the buffers are not being reused.
• Number of rows before transformations is usually not equal to the number of rows after.
• Need to read the entire input to process – Makes this slower
• A new thread must be created
• Example: Aggregate, Sort