Professional SQL Server™ 2005 Integration Services

19.3. Solution Summary

Company ABC has made the need to resolve this payment processing hurdle their top priority. They already have a custom software application that gives users the ability to break the bulk payments down to an employee level, but the application requires that the customer and invoice be identified. The solution is to use SSIS to develop a package that can process data from these heterogeneous data sources. This solution should require no human intervention on items that can be identified as paid-as-billed items. The solution should be as "smart" as possible and be able to identify bank items that have been manually identified before. Items that can't be identified will still need to be processed manually, but it is expected that this number should drop 20 to 40 percent.

In preparation for the design of the SSIS package, specification documents for the two input files, ACH and Lockbox, have been gathered. The files contain multiple payment transactions and can be sent by both the bank and ACH clearinghouse to specific folders. Your process should be able to access these folders and continuously look for new files to process. When a file is located in the input folder, it needs to be validated for proper format, for previous processing, so a file is not processed more than once, and for each payment to summarize and balance to the total deposit. Files not meeting these criteria will be rejected. Once a file is verified, each payment in the file should be examined ...