Sql-server – How to determine if a SSIS transform is non-blocking, partially blocking, or fully blocking

sql serverssisssis-2012

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.

Best Answer

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.