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.
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.
- Fuzzy Grouping
- Fuzzy Lookup
- Row Sampling
- Term Extraction
- Script Component – When configured to receive all rows..
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.
- Data Mining Query
- Merge Join
- Term Lookup
- Union All
Here, no blocking takes place. The row is process, and then it moves to the next step.
- Cache Transform
- Character Map
- Conditional Split
- Copy Column
- Data Conversion
- Derived Column
- DQS Cleansing
- Export Column
- OLE DB Command
- Percent Sampling
- Row Count
- Script Component – Except when configured for all.
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,