16 Mar

SSIS: Blocking, Non-Blocking And Semi-Blocking Transformations

This should have been a clean concept, and now that I’ve written this, it is, but, unfortunately, before I wrote this post, not so much.  As it showed up on the MeasureUp practice test, I figured I’d better write about it.  Especially since it turned out that I only knew it and didn’t KNOW it.

Blocking Transformations

The simplest version of this is an aggregate transformation because an aggregate must process every row in the transformation before it produces a total or in the case of a SORT, it must go through every record before it finishes.

These include:

  • Aggregate
  • Fuzzy Grouping
  • Fuzzy Lookup
  • Row Sampling
  • Sort
  • Term Extraction
  • Script Component – When configured to receive all rows..

Semi-Blocking Transformations

These partially hold up a transformation for processing and they don’t need to read the entire data set before processing.  However, something usually has to happen to the record before it can move on.  For instance, it’s part of a Join, and it needs to process it’s part of the join before it can move on.

These include:

  • Data Mining Query
  • Merge
  • Merge Join
  • Pivot
  • Term Lookup
  • Unpivot
  • Union All

Non-Blocking Transformations

Here, no blocking takes place.  The row is process, and then it moves to the next step.

These include:

  • Audit
  • Cache Transform
  • Character Map
  • Conditional Split
  • Copy Column
  • Data Conversion
  • Derived Column
  • DQS Cleansing
  • Export Column
  • Lookup
  • Multicast
  • OLE DB Command
  • Percent Sampling
  • Row Count
  • Script Component – Except when configured for all.
Worth getting.  Available at amazon.com.

Worth getting. Available at amazon.com.

Probably the best way to memorize,  I mean learn, these is to hit the blocking, and semi-blocking transformations and let the rest fend for themselves.

Finally, transformations are processed asynchronously or synchronously.  Non-blocking transformations are synchronous, while blocking and semi-blocking are asynchronous.

I used the following resources for this post.

Professional Microsoft SQL Server 2012 Integration Services – Available at Amazon.com

and this post by Jorg Klien,

SSIS – Non-blocking, Semi-blocking and Fully-blocking components