THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft-only BI consultant from the Netherlands

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 (dis)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    

Published Tuesday, February 12, 2008 11:17 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Joost said:

Good article, very clear!

December 23, 2010 10:09 AM
 

zorex said:

Usefull article, thanks a lot !

March 12, 2011 10:57 AM
 

Raj said:

Thanks, I have been in wrong notion about some of the transformations. But you cleared it thanks.

April 12, 2011 2:44 PM
 

Prateek said:

I am trying to build a Destinatino adapter whose error output can be something apart from integer(errocode and index). How can we proceed with that?

June 22, 2011 4:40 PM
 

venkat said:

It's pretty clear,pls give definition for Blocking,non-blocking or semi-blocking definition.

I am just trying to understand how it's behave in the sql server engine.

please excuse if i am wrong.

June 23, 2011 9:23 PM
 

knaidug said:

Very good article and very clear.

Thanks a lot.

June 30, 2011 2:08 AM
 

Ishwar said:

Awesome article. Probably you could create a package with one of the blocking, non blocking and semi blocking transformations and explain as to what exactly is happening @ runtime for each of the transformations.

July 2, 2011 6:42 AM
 

veeru said:

nice good article

July 5, 2012 2:43 PM
 

Tarun said:

Nice.. cleared my doubts

July 25, 2012 4:56 AM
 

Naga said:

Nice article,easy to undesrstand.

September 25, 2012 2:38 AM
 

Ganesh said:

Good post !

September 27, 2012 2:41 AM
 

Naidu said:

Good

November 27, 2012 4:51 AM
 

aditya said:

good explanation and good content.

December 18, 2012 6:06 PM
 

Suman said:

Good information on comparing various transformations

January 16, 2013 1:25 AM
 

Syed Hayath said:

Very Good

Clearly Understandable........:-)

Thank You

February 11, 2013 12:57 AM
 

Nag said:

Very good..

February 21, 2013 9:15 AM
 

nagireddy said:

very good article

March 1, 2013 6:14 AM
 

Chielus said:

The multicast component is partially blocking, not non-blocking.

http://social.technet.microsoft.com/wiki/contents/articles/7392.ssis-multicast-transformation.aspx

March 1, 2013 7:32 AM
 

Chielus said:

Also, the script component can be either of the 3 i think?

March 1, 2013 7:34 AM
 

waseem said:

HI,

Let say i have first name and last name as my input column but i m passing Name i.e  first name + last name  as  a  single  record (by concatanatin).So in this my  expression is recieving 2 inputs but  one  output.So can u please xplain why still expression is non blocking

March 19, 2013 12:47 PM
 

Thiru said:

Hi Waseem,

No of records IN== No of records out   (i.e 10(Firstname+Lastname)=10 Name)  So 10 input records 10 output records

March 31, 2013 2:49 PM
 

Vinod said:

Text is not visible properly. There are lot of hyperlinks on text.Please remove ads on top of text

April 4, 2013 5:27 PM
 

pramod said:

Thanks for the wonderful post.

Any idea why “Row sampling” is blocking task whereas “Percentage sampling” is non-blocking?

September 6, 2013 7:25 AM
 

Vinod said:

Lot of Ads in Site area ppearing over content in the website. Can you please move ads to other places so that content should be clearly visible.

December 20, 2013 9:42 PM
 

Papa said:

Nice Article

January 10, 2014 1:18 AM
 

Amit singh said:

so clear picture

March 15, 2014 11:56 PM
 

Maganti said:

Thanks.it's clear my doubt..

July 21, 2014 5:09 AM
 

srinu said:

It's Very GOOOOOOOOD Article

August 2, 2014 7:46 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement