Aim : This post is specifically written to understand difference between Non-Blocking transformations, Semi-Blocking transformations and Fully-Blocking transformations in SSIS ( SQL Server Integration Services). Have you ever thought about the behavior and working of the different transformations used in SSIS? Let’s read this article to understand better, if there is any method to recognize transformation category types and inner working of the SSIS transformations.
Description : In SSIS, we have 2 very important panes – Control flow pane and Data flow pane.
- Control flow acts as a brain of an SSIS package and contains various tasks and containers.
- On the other hand, Data flow contains numerous data flow components responsible for data transformation (manipulation).
If you have noticed the above statement carefully, there are 2 concepts which emerged out. We will further concentrate and discuss on these 2 concepts – One is Data flow components and the other is Data transformations.
Data flow components are the components (tools) with the use of which we can achieve Data transformations such as Sort component for sorting data, Multicast component for creating multiple copies of data, Aggregate component for summarized data and other transformation components for different needs.
Synchronous vs Asynchronous
Data flow components in SSIS are broadly classified into 2 types – Synchronous and Asynchronous components. In the table below, you can find a list of differences between Synchronous and Asynchronous components in SSIS.
NOTE – An important technical thing you need to notice about these 2 components is that, Synchronous reuse buffers and therefore are generally faster than Asynchronous (Which require new buffer).
Interview Question – Are Source and Destination adapters Synchronous or Asynchronous components?
Answer – All Source adapters are Asynchronous because they create 2 buffers – One buffer is for success output and other for the error output. On the other part, all Destination adapters are Synchronous components.
Non vs Semi vs Fully Blocking transformations
Data transformations in SSIS are divided into 3 categories – Non-blocking transformations, Semi-blocking transformations and Fully-blocking transformations. In the table below, I have summarized the differences between these 3 types.
SSIS Transformation categorization
All Transformations in SSIS are categorized as shown below in the table –
With this we have reached at the end of our post on differences between Non-Blocking transformations, Semi-Blocking transformations and Fully-Blocking transformations in SSIS ( SQL Server Integration Services). We hope you must have enjoyed this post. Kindly put in your valuable feedback, queries in the comments section below.