Professional SQL Server™ 2005 Integration Services

19.5. Data Architecture

This section will detail the data sources both in SQL Server and in the format of each of the input files. First, create default locations to simulate your receiving area for the external files, and then you'll take a closer look at each of the input files that are part of the business requirements.

19.5.1. File Storage Location Setup

Create a base directory to store the file-based imports to this project. Throughout the case study, the base location will be referred to as C:\casestudy\ for file-based imports. In the base directory, create two subdirectories: ACH\ and LOCKBOX\. You will use these locations to store the files you'll create in the next few sections.

19.5.2. Bank ACH Payments

Customers make payments within their own banks or electronic payment systems to ABC Company through an automated clearinghouse. The automated clearinghouse bundles up all the payments for the day and sends one XML file through an encrypted VPN connection to an encrypted folder. The bank wires contain only a little bit of information at the transactional level. Each XML file does contain a header row with a unique ID that identifies the file transmission. The header also contains a total deposit amount and a transaction count that can be used to further verify the file transmission. Each transactional detail row represents a deposit and contains two date fields: the date the deposit item was received and the date the deposit item was posted to ABC Company's deposit account. ...