Expert SQL Server 2005 Integration Services

by Knight, Brian; Veerman, Erik

• ISBN13: 9780470134115
• ISBN10: 0470134119
• Format: Paperback
• Copyright: 2007-05-29
• Publisher: Wrox

Summary

When it comes to developing an enterprise ETL system, moving from theory to practice often presents the biggest hurdle. Not only can the primary data processing tasks be complex in themselves, but add in administration, error handling, scalability, testing and deployment and the result is like juggling spinning plates. It's no wonder why ETL consumes over 60 percent of an enterprise data warehouse development effort. There are two keys to a successful ETL solution: using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements. With SQL Server 2005 Integration Services, half the battle is won. The out-of-the-box features provide the depth and breadth of functionality and flexibility needed to create scalable solutions. Even more, the usability and rapid learning curve reduce solution development time. The second ingredient is correct ETL architecture and design. When considerable effort is spent testing scenarios and proving out designs, valuable testing, implementation, and deployment time is lost. This book fills that gap. As a practical guide for Integration Services ETL development, you will learn ways to implement your ETL solution requirements from the data to the administration and everything in-between. Each chapter begins with a review of pertinent ETL concepts and moves into working those out into a design with multiple examples and related Integration Services features. Putting it all together to get a solution over the goal line requires knowing the big picture and coordinating all the pieces. The book, in short: ETL soup to nuts with Integration Services.

Author Biography

Brian Knight (SQL Server MVP, MCSE, MCDBA) of Green Cove Springs, Florida, is the co-founder of SQLServerCentral.com and JumpstartTV.com. He runs the local SQL Server users group in Jacksonville (JSSUG) and was on the Board of Directors of the Professional Association for SQL Server (PASS). He is a contributing columnist for SQL Server Standard and also maintains a regular column for the database web site, SQLServerCentral.com, as well as doing regular webcasts at JumpstartTV.com. He has co-authored and authored more than nine SQL Server books, including Admin911: SQL Server 2000 (McGraw-Hill Companies, 2001), Professional SQL Server 2000 DTS (Wiley Publishing, 2000), Professional SQL Server 2005 Administration (Wiley Publishing, 2006), and Professional SQL Server 2005 Integration Services (Wiley Publishing, 2006). He has spoken at conferences such as PASS, SQL Connections, and TechEd, as well as at many Code Camps. His blog can be found at www.whiteknighttechnology.com. He is an independent consultant at Pragmatic Works and spends most of his time trying to think about how to use the word onomatopoeia in everyday sentences.

Erik Veerman (SQL Server MVP, MCT, MCDBA) is a Mentor for Solid Quality Learning focusing on training, mentoring, and architecting solutions on the SQL Server Business Intelligence (BI) platform. His industry recognition includes Microsoft’s Worldwide BI Solution of the Year and SQL Server Magazine’s Innovator Cup winner. He has designed dozens of BI solutions across a broad business spectrum—telecommunications, marketing, retail, commercial real estate, finance, supply chain, and information technology. His experience with high-volume, multi-terabyte environments and SQL Server 64-bit has enabled clients to scale their Microsoft-based BI solutions for optimal potential. As an expert in OLAP design, ETL processing, and dimensional modeling, he is a presenter, author, and instructor. He led the ETL architecture and design for the first production implementation of SQL Server Integration Services (SSIS) and helped drive the ETL standards and best practices for SSIS on Microsoft’s SQL Server 2005 reference initiative (Project REAL). He also co-authored Professional SQL Server 2005 Integration Services (Wiley Publishing, 2006). As a resident of Atlanta, Georgia, Erik is a leader of the local Atlanta SQL Server User’s Group, a PASS and INETA user group chapter.

Table of Contents

Acknowledgments

Introduction

Getting Started

Extending Scripts in SSIS

Data Extraction

Dimension ETL with SSIS

Fact Table ETL

Processing Analysis Services Objects with SSIS

Package Reliability

Deployment

Managing SSIS

Handling Heterogeneous and Unusual Data

Migrating from DTS to SSIS

Scaling SSIS

Index

Table of Contents provided by Publisher. All Rights Reserved.