Professional SQL Server™ 2005 Integration Services

19.4. Solution Architecture

Before you jump into building this integration service, you should lay out what you want to accomplish. You have two sets of tasks: first, to import files of different formats, to validate, and to load them into your data structures; and second, to process the payments to find customer and invoice matches. Figure 19-2 shows a design where the logic is divided into two packages. Since the main job of the first task is to load, you'll name the first package CaseStudy_Load. This package will perform the loading process for each of the three import types. (There is also a good argument for having three separate packages: CaseStudy_ACH_Load, CaseStudy_LB_Load, and CaseStudy_Epay_Load.) The identification logic to apply to each transaction in the payment data is universal, so it makes sense to put this logic into a separate package. You can name this package CaseStudy_Process.

Figure 19.2. Figure 19-2

When you are building packages that have external dependencies such as file hierarchies, it is a good idea to programmatically validate the locations for existence before processing. You'll check for default paths and create them if they don't exist. If files for the Lockbox or ACH processes exist, you should read the files, parse the transaction information, validate totals against a control record in the file, and then persist the information from the file ...