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

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 into permanent storage. You should also confirm the acceptance of the transaction with the bank. The toughest part of this processing logic is that the validation routines have to validate file formats, proper data types, and file balances and check for file duplication. When processing any flat file from an external source, be aware of how that import file was generated and what you might find in it. Don't be surprised to find that some systems allow a date of 02/30/2005 or amount fields with data like .0023E2.

The downloaded bank transactions for the PayPal or E-Pay transactions will be easier to process — at least from an import standpoint. You only need to read information from a table in another OLE DB-compliant data source. You'll be creating a batch from the transactions, so balancing also shouldn't be an issue. The hardest part will be identifying these payments, since usually only an amount and an e-mail address are embedded in the transaction. All this information can be summarized in a flowchart like the one in Figure 19-3.

Figure 19-3

In the CaseStudy_Process package, you are required to complete a matching process of the payment information to find customers and invoices. You'll first attempt a high-confidence match using an exact match to the invoice number. If a match is not made, you'll move to a fuzzy lookup on the invoice number. If a match is still not made, you'll keep moving down to lower-confidence-level matches until you can retrieve an invoice or at least customer identification. Transactions identifiable only by customer will be checked against available invoices for a match within a billed-to-paid tolerance of 5 percent. Transactions that simply don't have enough certainty to be identified will be left at this point to subject-matter experts who will individually review and research the transactions to identify the customer or refund the payment. Research can be saved via software outside this SSIS project into the CustomerLookup table. A summary flowchart for the CaseStudy_Process package is shown in Figure 19-4.

Figure 19-4