SSIS – Non Blocking , Partially Blocking and Full Blocking

Data flow transformations in SSIS use memory/buffers in different ways.  The way transformation uses memory can impact the performance of your package.  Transformations memory/Buffer usage are classified into 3 categories:

1.Non Blocking  2.Semi Blocking  3. Full Blocking

All the dataflow components can be categorized to be either Synchronous or Asynchronous.

Synchronous vs Asynchronous : 

• Synchronous components The output of an synchronous component uses the same buffer as the input.  Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input.                                                                                                                               Number of records IN == Number of records OUT.

• Asynchronous components The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records than input records.

One Main thing is that synchronous components reuse buffers and therefore are generally faster than asynchronous components.

All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output.

All destination adapters on the other hand, are synchronous.

Here are some tips that will help you minimize your use of asynchronous transformations:

• Instead of using a Merge Join in your Data Flow, perform the joins in the source query or in a transform environment.
• If you absolutely have to use Merge Join, make sure that you sort the data in your source query. In the Advanced Editor, set the “IsSorted” property to true, and set the Sort Key Position on the Output Columns to the appropriate values.
• Instead of using an Aggregate transform do perform a count, consider using Row Count, which is a synchronous transformation.
• If you are using a Pivot or Unpivot transformation and it is performing poorly, consider using staging tables in your solution in order to leverage your SQL server environment to perform these transformations instead of doing it in SSIS.
• Even though Union All is a semi-blocking asynchronous transformation, you will probably not achieve better performance by replacing the Union All with multiple inserts.

SSIS transformations categorized: