Microsoft® SQL Server® 2008 Integration Services: Problem-Design-Solution

2

SSIS Management Framework Design

Before designing any SSIS packages, you must decide on a number of factors that will affect the design of your packages. These factors include auditing, configurations, and monitoring.

This chapter contains one Problem-Design-Solution. The “Problem” section discusses the issues you will face in trying to manage these factors across developers and departments, and the fact that you can create a standard framework implementation to avoid these issues. The “Design” section discusses in detail how to create a standard SSIS management framework implementation. Finally, the “Solution” section shows how to implement and deploy the framework in SSIS.

Problem

Whether you are creating an extraction, transformation, and loading (ETL) package, a maintenance package, or a simple process workflow, certain requirements are the same for every package. As a developer, you want to decrease your development and maintenance times. The rest of your team also has requirements for the package, which includes the following:

• A support person must know how, when, and why a package failed.
• A system administrator must see when that package executed and how long it took.
• A change control operator must promote the package through environments with as little change as possible.

It comes down to you to put all of these requirements into every package that you create.

Challenges of Not Having a Framework Implementation

You will face many hurdles when coming up with an approach ...