Microsoft SQL Server 2005 Integration Services

ForewordForeword

Microsoft SQL Server 7.0 and SQL Server 2000 both contained a component called Data Transformation Services (DTS). DTS was an ETL tool. If you don't already know that ETL means Extract, Transform and Load, this might not be the book for you. But if you do care about ETL, have challenging ETL problems, and have SQL Server 2005, this most likely is the book for you! This book is about SQL Server 2005 Integration Services, the successor to DTS.

It took a unique team to build SQL Server 2005 Integration Services. If I had told you in 2000 when we started the Integration Services project that we would assemble a team of almost 30 people at Microsoft who were utterly passionate about ETL, you would have been skeptical. Through the leadership of Kamal Hathi, Donald Farmer, Eduardo Alvarez-Godinez, and your author Kirk Haselden, we did just that.

When we began planning "Yukon" (which became SQL Server 2005), we knew that our customers were facing ever larger and more challenging ETL problems. Years ago, data warehousing and business intelligence were "local" problems; or perhaps better said as "solutions to local problems."—the sales warehouse sourced answers to questions in the sales department, and marketing had its own warehouse. True performance management, the evolution of business intelligence, however, requires a complete company view. Customers want to load far more data than in the past, and they want to do so far more frequently.

We knew it was time to reinvent our ETL tool. We wanted to increase performance by at least an order of magnitude. We wanted to incorporate best-of-class ease of use, unparalleledprogrammability, and a very high level of out-of-the-box functionality. We truly sought to reinvent our ETL offering. Thinking about who in our industry really knew how to push a lot of processing against a lot of data, I sought out programmers and architects from the compiler team. We were lucky to find Mike Blaszczak, who had made key contributions to MFC, and who is one of the best programmers in the world. Mike in turn knew Kirk, and brought him into the team early on. In just a few years, Kirk became the Development Manager for Integration Services, and with his team, brought it to market.

This makes Kirk an ever-so appropriate guide for you in your exploration and use of Integration Services. He of course has a deep understanding of SSIS at every level—we expect that from a Development Manager. Beyond that, he has a love for users and customers that translates into a clear writing style and an enjoyable read. In each chapter, Kirk motivates you, teaches you, and delivers insight only a "Dev Manager" would possess. Pay attention to the notes in various chapters: This is where you get to see into the mind of the developer. Also, pay attention to the quotes that open each chapter—I can verify many of these, I was there to hear them, and they are often entertaining.

One of the ways in which SSIS improves on DTS is in manageability. The "configuration" is a new concept in SSIS. Configurations allow ETL developers to put some of the metadata of their package on the outside of the package, so that administrators can tweak them at deployment time. Chapter 14, "Configuring and Deploying Solutions," covers this in detail. Chapter 23, "Data Flow Task Internals and Tuning," covers optimization and tuning. SSIS can handle enterprise ETL needs, and this chapter gives you the insight and details you need to run your SSIS packages at their fullest performance.

Another area of difference between DTS and SSIS is extensibility. Chapter 24, "Building Custom Tasks," covers the development of custom tasks. Chapter 25, "Building Custom Data Flow Components," covers the development of custom components. The companion website at http://www.samspublishing.com includes all of the source code that Kirk develops in these chapters. I'm personally looking forward to using his source component to pull EXIF information out of JPEG files: Kirk is an avid photographer and has used real-world examples throughout the book.

It's been a privilege to work with Kirk for several years. I hope to work with him for years to come. He worked hard on this book because he loves SSIS and its customers. I hope you enjoy his work and profit from this book.

Bill Baker
May 2006, Redmond, WA

© Copyright Pearson Education. All rights reserved.