Professional SQL Server™ 2005 Integration Services

19.1. Background

Company ABC is a small benefits company that sells several niche products to other small business owners. They offer these products directly to the employees of the small businesses, but the employers are billed, not the employees. Company ABC considers the employers to be their customer and creates monthly invoices for the employee-selected services. Each invoice contains an invoice number for reference purposes. Company ABC's customers deduct the cost of the products from the employee paychecks. These payments are then submitted back to Company ABC in a lump sum, but because of timing issues and ever-changing worker populations, the payment doesn't always match the billed amount. Customers have the option of paying invoices using one of the following payment methods:

• Pay by using PayPal or an e-mail payment service. These services directly credit a corporate bank account and typically provide a small description of the service being paid, the amount, and an e-mail address or other type of surrogate user identity. These entries are downloaded daily from an online bank account and are available within an OLE DB–compliant data source.

• Pay by check. The customer sends a copy of the invoice and a check in the mail to a special address that is serviced by a bank. The invoice could fully, partially, or not even accompany the payment. The bank credits the account for each check received and provides an output file containing as much data as practicable from the supporting ...