O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required