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: https://jorgklein.com/2009/04/14/ssis-lookup-is-case-sensitive/

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.

  • 3
    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 '14 at 22:09
  • 2
    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 '14 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 '14 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. – Nick.McDermaid Aug 24 '14 at 10:10
  • 1
    For those that might want to test this tip goes over testing of semi-blocking transforms – user507 Dec 22 '14 at 8:23

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers.

An operations which is partially-blocking writes data onto new buffers, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated.

An operation which is non-blocking can have the subsequent operation working on the same buffer, as each row is handled.

But many texts will refer to 'partially-blocking' as 'non-blocking', as the behaviour is much closer to 'non-blocking' than 'blocking'.

You should be able to tell what's going on by watching the data flow run, and seeing when the numbers are increasing at each point.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.