Professional SQL Server™ 2005 Integration Services

19.6. Case Study Load Package

The import integration service will be the first of the two packages you will build for this project. To keep this from becoming a 100-step process, you'll break it up into several sections: Naming Conventions and Tips, Package Setup and File System Tasks, Lockbox Control Flow Processing, Lockbox File Validation, Lockbox Processing, ACH Control Flow Processing, ACH Validation, ACH Processing, Email Control Flow Processing, and Email Data Flow Processing. Each step will be explained in detail the first time, and as things become repetitive, you'll pick up some speed.

19.6.1. Naming Conventions and Tips

There's nothing like opening up a package that fails in production and seeing tasks named Execute SQL Task, Execute SQL Task1, and Execute SQL Task2. What do they do? There is also something to be said when there is so much annotation that it is a nightmare to maintain. The balance depends on your philosophy and your team, but for now, the following rules seem to make sense in your SSIS development processes.

• Name the package. Name it something other than package.dtsx.

• Name packages with ETL verb extensions: <PACKAGE NAME>_Extract, <Package Name>_Transform, or <Package Name>_Load. The extension _Process seems to be explicit enough for those packages that don't fall into the other three categories.

• Provide some brief annotation about what the package does, where it gets inputs and outputs, and what to do if it fails. Can it be rerun again? Is it part of ...