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

Contents

Introduction

Chapter 1: SSIS Solution Architecture

Problem

Macro Challenge: Enterprise Data Spaghetti

Micro Challenge: Data-Processing Confusion

Problems with Execution and Troubleshooting

Infrastructure Challenges

Other Challenges

Design

Choosing the Right Tool

Overall Solution Architecture

Data Integration or Consolidation

Data Warehouse ETL

Project Planning and Team Development

Agile Benefits

Agile Cautions and Planning

Data Element Documentation

Package Design Patterns

Modular Packages

Master Packages

Server and Storage Hardware

Server Hardware

Development and Test Servers

ETL Collocation

Storage Hardware

Package Execution Location

Package Storage Location Versus Execution Location

Execute SQL Task and Bulk Insert Task Execution

Package Execution and the Data Flow

Design Review

Solution

Setting the Stage: Management and Deployment

Source Data: Files, Tables, and Data Cleansing

Data Warehouse ETL and Cube Processing

Advanced ETL: Scripting, High Availability, and Performance

Summary

Chapter 2: SSIS Management Framework Design

Problem

Challenges of Not Having a Framework Implementation

Different Development Methods

Changing Metadata

Getting the Right Information to the Right People at the Right Time

Reusability

Framework Implementation

Configuration Management Scheme

Logging and Auditing

Template Package

Framework Benefits and Value

Design

Configuration Management

Overall Design

Environment Variable Configuration

XML Configuration File

SQL Server Configuration

Logging ...