liveBook · Manning

Blocking transformation

As explained in 100 SQL Server Mistakes and How to Avoid Them

A blocking transformation withholds all output until it has received and processed all input rows.

A blocking transformation withholds output until it has received and processed every input row. Downstream components cannot begin until processing finishes. section 6.3.2

Recognize the distinction:

  • Non-blocking transformations, such as Derived Column, output processed batches as soon as they are ready. section 6.3.2
  • Aggregate is blocking because grouped totals or averages cannot be confirmed until no more rows can change them. section 6.3.2

Grouped campaign averages

An Aggregate transformation calculates average BidPrice and CostPerMille for each unique CampaignID, CountryCode, and EventTime combination. It must receive all rows before releasing those averages. section 6.3.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Defines blocking behavior and explains its correctness rationale through grouped aggregation, without discussing implementation internals.
Examples
Uses grouped average BidPrice and CostPerMille values keyed by CampaignID, CountryCode, and EventTime.
What you can do after
Evaluate whether an SSIS transformation can emit batches immediately or must wait for complete input.
How it connects
Connects Aggregate transformation with non-blocking transformations, Derived Column, grouping, and downstream data-flow components.

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.