THE SQL Server Blog Spot on the Web

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

Jorg Klein

Microsoft Data Platform MVP 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:





Synchronous or asynchronous




Number of rows in == number of rows out


Usually False

Usually False

Must read all input before they can output




New buffer created?




New thread created?


Usually 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  
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



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:


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.

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:


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:'s clear my doubt..

July 21, 2014 5:09 AM

srinu said:

It's Very GOOOOOOOOD Article

August 2, 2014 7:46 AM

Kirti Sardana said:

Short and Crisp ..Thanks :)

October 8, 2014 4:50 AM

Chinna Narayan Reddy said:

Great article

October 15, 2014 5:36 AM

Vikas said:

Little confusion

for Synchronous Row input = Row output

Then how Percentage Sampling can be Synchronous

November 24, 2014 11:39 PM

vivek raj said:

Is union all is semi-blocking transformation? I think it is non-blocking transformation because it does not need another buffer to store result.

December 11, 2014 12:41 AM

John Jeyaraj said:

Really nice article . thanks.

May 31, 2015 2:37 PM

kumaresh said:

Very good article. Useful..

July 10, 2015 9:46 PM

Kumar said:

Nice Article

October 28, 2015 2:45 PM

Ram said:

it's Good

November 20, 2015 6:11 AM

learner said:

It's useful and good.


December 25, 2015 11:13 PM

Teja said:

Excellent Article

March 5, 2016 10:22 PM

Mike T said:

That's really interesting, considering it reverses the customary meaning of synchronous and asynchronous.  Typically calls that are synchronous are blocking calls.

March 13, 2016 12:10 AM

bhaskar said:

really good article

April 7, 2016 7:22 PM

rajesh said:

Nice explanation.

Thanks a lot.

April 12, 2016 11:29 PM

KP said:

Very good explanation. clear and precise

July 26, 2016 5:59 PM

vamshi said:

very useful info. thank you

August 7, 2016 1:03 AM

Rajesh said:

Fantastic article

December 19, 2016 2:14 AM

Santhosh said:

Nice Article :-)

December 27, 2016 4:05 PM

shaheed j said:

Very basic but powerful explanation nice job

January 12, 2017 2:01 AM

GOPI said:

You mentioned, All source adapters are asynchronous(two outputs) but you mentioned Lookup , multi cast(these will have more outputs..matched/non matched..etc) are non blocking transformations. Is not this contradict?

February 2, 2017 6:21 AM

Swami said:

Useful article

February 28, 2017 6:59 AM

Leave a Comment


About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement