Professional SQL Server™ 2005 Integration Services

19.7. Case Study Process Package

The Load package put the data into the database. The Process package is going to perform the magic. All this payment data from different sources with varying degrees of quality needs to be matched by invoice or customer attributes against your dimension tables of Invoice and Customer. Having it combined in one place allows this package to apply the logic of payment matching to all payments at once. Hopefully, every time the package runs, it is money in the bank for Company ABC.

The strategy for this package is to mimic the logic provided from the business specifications in Figure 19-4. You will queue all the payment transactions that are unmatched for a moment in time. Then you will run that stream of payments through a gauntlet of matching options until you break through your confidence level for matching. This design will make it easy to add further matching scenarios in the future but will allow you to use the advanced fuzzy matching logic available today in the Integration Services.

You'll be breaking the construction of the package into these sections: Package Setup, High-Confidence Data Flow, and Medium-Confidence Data Flow.

19.7.1. Package Setup

This portion of the Case Study will create the Control Flow steps that you need to systematically review pending and unmatched payment transactions. You will set up the variables that you need to store unmatched payment counts at each stage of the matching process. You will create placeholder Data ...