Avoid the asynchronous transformation as much as possible

It is a bit confusing if you know asynchronous processing in Service Broker or an interface application because in these platforms asynchronous processing is implemented for parallel and faster execution. But in SSIS asynchronous means that input records are not processed individually. Output is derived from the full or a part of input recordset. For example in Sort operation the first record can be decided only after checking all records.

 

The one of most important strength of SSIS is it’s buffer-oriented architecture to efficiently load and manipulate datasets in memory. The benefit of this in-memory processing is that you do not need to physically copy and stage data at each step of the data integration. Rather, the data flow engine manipulates data as it is transferred from source to destination. But because of asynchronous transformation SSIS looses its in-memory processing strength. Because asynchronous transformation task’s uses extra memory and the output recordset is buffered in separate memory than the input recordset. All blocking tasks (Aggregate and Sort) and partially blocking tasks (Merge, Merge Join, and Union All) are Asynchronous transformation.

 

Design Description of Alternative 1	Design Description of Alternative 2
In this design, a Script Component generates 100,000,000 rows that first pass through a lookup. If the lookup fails because the source value is not found, then an error record is sent to the Derived Column transformation where a default value is assigned to the error record. After the error processing is complete, the error rows are combined with the original data set before loading all rows into the destination.	Like Design Alternative 1, this design uses the same Script Component to generate 100,000,000 rows that pass through a lookup. Instead of handling lookup failures as error records, all lookup failures are ignored. Rather, a Derived Column transformation is used to assign values to the columns that have NULL values for the looked up column.
With two execution trees in this scenario, the biggest performance bottleneck is related to the extra copy of the data in memory created for the Partially Blocking Union All transformation.	The performance of this solution is approximately 21% faster than Design Alternative 1. With one execution tree in this scenario, the operations are consolidated and the overheard of copying data into a new buffer is avoided.