Book Review – Professional SQL Server 2005 Integration Services by Brian Knight, Allen Mitchell et al.

With the advent of SQL Server 2005, Microsoft is providing a completely new enterprise extraction, transformation and loading product called SQL Server Integration Services (SSIS). This basically throws out the old Data Transformation Services (DTS) in favor of SSIS.

Professional SQL Server Integration Services is intended for developers, DBA’s and the casual user who hopes to use SSIS for transforming data, creating a workflow, or maintaining their SQL Server. The authors assume that you are familiar with the fundamentals of SQL Server, and that you have some rudimentary programming skills. While they do reference DTS, there is no requirement to know it.

This book is structured into 19 chapters. The first four are foundation work and will be covered together. The remaining will mostly be covered on their own, although I will combine a couple based on similarity of topic. To use this book, you only need SQL Server 2005 and the Integration Services component installed.

Chapters 1 thru 4 introduce the basic concepts of as well as the architecture of SSIS. There is a brief overview of what you can do with SSIS. You will then be shown how to import and export data by using the wizards provided, and you will be given a tour of the Business Intelligence Development Studio (BIDS). You will see each of the tasks that are available to you in SSIS. Finally you will be shown how to use containers to do looping and how to configure each of the basic transformations.

Chapter 5, “Creating an End-To-End Package,” will get you into some practical applications for SSIS. You will start by creating a package to import flat files into SQL Server, subsequently adding more complexity, and finally creating a package that handles basic errors as well.

Chapter 6, “Advanced Tasks and Transforms,” takes what you learned in chapter 4 about working with container and data flow, and provides more advanced techniques to get the job done. You will put these new tools to work on some real world problems.

Chapter 7, “Scripting in SSIS,” will show you how to use the greatly enhanced custom scripting feature using Visual Basic.Net. By using the different scripting options, you will be able to control things like execution flow or to perform custom transformations.

In Chapter 8, “Accessing Heterogeneous Data,” you will learn how to import and work with data from non-SQL server sources. These could be other database systems such as Oracle, Office documents or even XML.

Chapter 9, “Reliability and Scalability,” covers how to handle errors, restart packages, set up check points and work with transactions. Also how to stage data and scale across machines.

Chapter 10, “Understanding the Integration Services Engine,” and Chapter 11, “Applying the Integration Services Engine,” will take you under the hood of SSIS to understand the architecture of the engine and its core concepts. Then you will bridge the gap between understanding the features and designing a solution based on those features.

Chapter 12, “DTS 2000 Migration and Metadata Management,” will show you how to migrate your DTS 2000 packages to SSIS as well as, if you need to, how to run DTS packages under SSIS.

Chapter 13, “Error and Event Handling,” will take you through controlling the package workflow. From the highest level, by using precedence constraints, and drilling down to event handling, you will see how to use trappable events to handle your logging, and see how they can be used for debugging and troubleshooting your system.

Chapter 14, “Programming and Extending SSIS,” will show you how you can create your own tool by using the pipeline programmability in SSIS when you cannot find an existing tool. Chapter 15, “Adding a User Interface to Your Component,” expands on chapter 14 by allowing you to add a UI to your pipeline component.

Chapter 16, “External Management and WMI Task Implementation,” provides an overview of how you can externally manage your packages through managed code via the Microsoft.SqlServer.DTS.Runtime namespace.

Chapter 17, “Using SSIS with External Applications,” will show you three examples of how external applications utilize SSIS. These include using RSS feeds to import data, working with InfoPath documents, as well as generating output to an ASP.NET application

Chapter 18, “SSIS Software Development Life Cycle,” discusses some of the development lifecycle features available in Microsoft Team System by creating a Team Project.

Chapter 19, “Case Study: A Programmatic Example,” will take you through a case study to try to give you a bigger scope of what SSIS can do.

Professional SQL Server Integration Services is a book that provides a good overview of all of the functionality that is contained within Microsoft SSIS. Most of the book is centered around BIDS, and highlights pretty much everything that can be done with SSIS. It crosses between tutorial and reference and is a fairly easy read. At times the book does seem hurried, but overall the content is all here and is easy enough to figure out.