Introduction

Overview

A complete database solution requires data to be integrated from a variety of sources. One of the greatest challenges facing business today is that important business information exists in multiple locations and in different formats. As an industry, we have empowered business leaders and information workers with access to corporate data and powerful analysis tools. With access to so much information, decision makers need one indisputable version of data. A reliable ETL (extract, transform, and load) process is the backbone of business-critical data consolidation and business intelligence (BI) services used to lead and support business direction. Microsoft SQL Server 2005 Integration Services (SSIS) provides a foundation to design and perform effective ETL processes.

The goal of this book is to help you design and implement ETL solutions as quickly as possible, both in concept and in practice. You will learn and understand the core principles and concepts of effective data transformation. Through simple hands-on exercises, you will quickly learn to design Integration Services packages used to transform data between files and relational databases; handle conditional logic; and to alter, split, match, merge, combine, and join data in a data flow. After completing these exercises, you will know how to use the appropriate tasks, transformations, connection managers, and data source and destination adapters in concert to form SSIS packages. You will learn to deploy, configure, and optimize packages to run on production servers.

This book is written to address the requirements of professionals with different needs. Database administrators and application developers need to transform data to support specific applications. BI system architects require data to be consolidated from multiple source systems to a central data warehouse or data mart. A scheduled ETL package must be flexible enough to handle errors and data anomalies. Whether you need to run a package to perform a quick, one-time data import or you need a scheduled process to populate the corporate data warehouse every night, you will learn to design an Integration Services solution to meet that need.