Synchronous and Asynchronous

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

How can you recognize these three component types, what is their inner working and do they acquire new buffers and/or threads? Synchronous vs Asynchronous The SSIS dataflow contain three types of transformations. They can be non-blocking, semi-blocking or fully-blocking. Before I explain how you can recognize these types and what their properties are its important to know that all the dataflow components can be categorized to be either synchronous or 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 then input records. The only thing you need to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that need a new buffer. 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. Non-blocking, Semi-blocking and Fully-blocking In the table below the differences between the three transformation types are summarized. As you can see it’s not that hard to identify the three types. On the internet are a lot of large and complicated articles about this subject, but I think it’s enough to look at the core differences between the three types to understand their working and (dis)advantages: Non-blocking Semi-blocking Fully-blocking Synchronous or asynchronous Synchronous Asynchronous Asynchronous Number of rows in == number of rows out True Usually False Usually False Must read all input before they can output False False True New buffer created? False True True New thread created? False Usually True True All SSIS transformations categorized: Non-Blocking transformations Semi-blocking transformations Blocking transformations Audit Data Mining Query Aggregate Character Map Merge Fuzzy Grouping Conditional Split Merge Join Fuzzy Lookup Copy Column Pivot Row Sampling Data Conversion Unpivot Sort Derived Column Term Lookup Term Extraction Lookup Union All Multicast Percent Sampling Row Count Script Component Export Column Import Column Slowly Changing Dimension OLE DB Command