Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are several blogs which claim to categorize SSIS Transforms into blocking (asynchronous), non-blocking(synchronous) and partially blocking(asynchronous).

When looking into the specific question: Is multicast synchronous(non-blocking) or asynchronous(partially blocking)?

One resources claims async: "The Multicast is an asynchronous (also known as partially blocking) transformation" source: http://social.technet.microsoft.com/wiki/contents/articles/7392.ssis-multicast-transformation.aspx

and another syncronous: http://sqlblog.com/blogs/jorg_klein/archive/2008/02/12/ssis-lookup-transformation-is-case-sensitive.aspx

Other resources claim the DQS Cleansing transform is a non-blocking transform, but it seems to me to be partially blocking.

Please don't answer with a link to a list of transforms categorized by type. The answer to this question will hopefully enable a more rigorous method to PROVE a correct answer.

As the partially-blocking and blocking transformations will move data to new buffers as opposed to the non-blocking which operate on the buffer in place, I suspect that the solution will be to watch buffer creation during transform execution, but I am unsure (a) if this will produce the definitive answer and (b) how to do this.

I believe that a non-blocking transform will not cause a new execution tree to begin, so it is possible that the answer lies in logging the PipelineExectionTrees and PipelineExecutionPlan. This may differentiate between synchronous and asynchronous, but may not differentiate between partially blocking and fully blocking.

share|improve this question
Mulitcast is synchronous. The quick sniff test for synch vs async is whether the lineageid (pre 2012) remains the same before and after a transformation. Let me look into 2012 and how that works now. –  billinkc Feb 10 at 22:09
Blocking behaviour and synchronicity are not the same thing. Synchronicity is about whether the data needs to be moved into a new buffer or not, whereas blocking is about whether each row can be handled immediately or not. For example, Union All is non-blocking, but asynchronous - it creates a new buffer and a new execution thread, but doesn't need to wait to do that. –  Rob Farley Feb 11 at 20:40
@Rob - while I agree they are not the same thing (asynchronous can partially or fully block) - The jorg_klein blog actually states that the Union All is partially blocking. I am inclined to agree as if the data is moved into the new buffer, intuition leads me to think that would partially block. If he can be proved wrong and my intuition is incorrect, all the better as that would answer the question! –  Thronk Feb 11 at 21:27
Does anyone have any ideas about how this could be tested? I guess performance or informational logs could be used to find this out for sure but I'm not exactly sure which. –  ElectricLlama Aug 24 at 10:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.