The preferred pattern for an ETL SSIS package for a Blackbaud Data Warehouse uses a Transact-SQL MERGE statement. For large sets of updates, this offers superior performance to an alternative, Wishbone, pattern. With the Wishbone pattern, new rows are inserted along one path in the flow and rows to be updated are processed along another path. Updates performed through the Wishbone pattern are problematic because each update requires a check against every row.
Many existing packages follow the Wishbone pattern. Generally this pattern works well when the goal is to create new rows. Some packages follow the Merge pattern and others include enough extra logic to consider the package to be another pattern entirely. For example, BBDW_DIM_APPEAL.dtsx is an example of a package that follows the Wishbone pattern. BBDW_FACT_INTERACTIONRESPONSE.dtsx is an example of a package that follows the Merge pattern. But the Control flow for BBDW_DIM_CONSTITUENT.dtsx diverges so much that it does not fit either pattern. Both patterns are presented.