Non-blocking, semi-blocking, and blocking transforms in SSIS

Not all Data Flow transforms are created equal. In fact, some are much faster and require a lot less memory (non-blocking transforms). Others are slower while consuming much more memory (blocking transforms), and some are in between (semi-blocking transforms). We don’t always have a choice if we need to use a certain transform to do a certain specified task, but it is good to know the differences between these types of transforms so that we can better understand performance and resource utilization issues.

Synchronous and asynchronous

There is another related aspect about Data Flow transforms, which is their ability to quickly process a row as they are coming into the transform, independently of any other rows that came before or after (synchronous transforms). The other type of transform needs to be dependent on some or all of the rows that come before and after (asynchronous transforms).

On the whole, non-blocking transforms are also synchronous, thus are fast and require less memory; they process a row as soon as it arrives and sends it on its way to the next destination. Alternatively, both blocking and semi-blocking transforms qualified as asynchronous; they need to wait and collect some or all the records before being able to process them before sending them to the next destination.

The output of a synchronous component utilizes the same buffer as the input component because the output of a synchronous component always contains exactly an equal number of records as the input component. This reuse of buffers makes it faster. Alternatively, the output of an asynchronous component uses new buffers. New buffers are required because an asynchronous component can have more or less output records than input records.

It’s important to note that all source adapters are asynchronous; they create two buffers: one for the success output and the other for the error output. Destination adapters are synchronous.