SSIS – Non-blocking, Semi-blocking and Fully-blocking components

How can you recognize these three component types, what is their inner working and do they acquire new buffers and/or threads?

Synchronous vs Asynchronous

The SSIS dataflow contain three types of transformations. They can be non-blocking, semi-blocking or fully-blocking. Before I explain how you can recognize these types and what their properties are its important to know that all the dataflow components can be categorized to be either synchronous or asynchronous.

·         Synchronous components
The output of an synchronous component uses the same buffer as the input. Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input. Number of records IN == Number of records OUT.

·         Asynchronous components
The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records then input records.

The only thing you need to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that need a new buffer.

All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output. All destination adapters on the other hand, are synchronous.

Non-blocking, Semi-blocking and Fully-blocking

In the table below the differences between the three transformation types are summarized. As you can see it’s not that hard to identify the three types.
On the internet are a lot of large and complicated articles about this subject, but I think it’s enough to look at the core differences between the three types to understand their working and (did)advantages:

 

Non-blocking

Semi-blocking

Fully-blocking

Synchronous or asynchronous

Synchronous

Asynchronous

Asynchronous

Number of rows in == number of rows out

True

Usually False

Usually False

Must read all input before they can output

False

False

True

New buffer created?

False

True

True

New thread created?

False

Usually True

True

All SSIS transformations categorized:

Non-Blocking transformations Semi-blocking transformations Blocking transformations
Audit Data Mining Query Aggregate
Character Map Merge Fuzzy Grouping
Conditional Split Merge Join Fuzzy Lookup
Copy Column Pivot Row Sampling
Data Conversion Unpivot Sort
Derived Column Term Lookup Term Extraction
Lookup Union All  
Multicast    
Percent Sampling    
Row Count    
Script Component    
Export Column    
Import Column    
Slowly Changing Dimension    
OLE DB Command