Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 329

Consider the case of financial data. Institutions that store or process financial data
are subject to frequent and comprehensive audits, and if inconsistent data appeared in a
governmental review of the data, it could spell disaster for the organization and its of-
ficers. Even though a data warehouse may not be subject to the same to-the-penny
auditing scrutiny as OLTP systems, there is still an expectation of consistency when
matters of money and governmental regulation are involved. In the case of a data ware-
house load where nonconforming data is encountered, quite possibly the best thing to
occur is that the package fails gracefully, rolling back any changes made as part of the
load.
Fail Package on Error
Extending the financial data example mentioned previously, let's examine the design
pattern to facilitate a failure in the event of a lookup error. In reality, this is the default
behavior. As shown in Figure 11-12 , we use the default setting of Fail Component on
the lookup components, which stops the execution of the package if a row is en-
countered that cannot be matched to either the GL Account or GL Subaccount lookup.
Figure 11-12 . Allow the package to fail upon error
It's worth noting here that to properly use this design pattern, you must include
some logic to roll back changes due to a partial load. If you encounter a fact row that