Reza Rad - Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Data Transformation Services (DTS) was Microsoft's first entrance into the world of advanced data transformation and task-oriented tools, allowing users to rapidly move data from one point to another, or to perform common tasks such as FTPing files from one server to another. New to SQL Server 2000, this tool was the foundation for many developers' toolkits. The UI was easy to use and understand, precedence constraints could be applied between tasks ensuring business rules were maintained, and custom code could be added to perform advanced tasks not found in the boxed feature set. DTS is still the bar against which many measure SQL Server Integration Services.

SQL Server Integration Services (SSIS), introduced in SQL Server 2005 and largely unchanged through SQL Server 2008 R2, was a rewrite of both the toolset and the paradigm by which developers were used to thinking as compared to the relatively easy-to-use DTS. SSIS has its strengths in separating the work surface of a DTS package into distinct parts, the Control Flow and the Data Flow. The Control Flow is designed to direct the "flow" of the package, ensure dependencies are met before executing a downstream task, perform looping operations over a varied list of sources, execute SQL statements, and so on. A Data Flow Task is designed to move data from one source to another, transforming data along the way. The separation allows for greater flexibility in developing a package by limiting the scope of what a developer can edit at once, and by allowing specific tasks to be copied and subsequently reused.

SSIS is not without its list of negatives, however. Through SQL Server 2008 R2, an SSIS package was a single entity, which could be executed in any number of places from within Business Intelligence Developer Studio, from the filesystem, or on a SQL Server instance. In a shop that has a large number of packages deployed, it was extremely difficult to manage all of the packages and track all of the activities that the packages were doing. This meant that developers were forced to write their own logging solutions to capture data such as row counts, start and end times, audit information, and any other pertinent information necessary to support the package. SSIS also has a steep learning curve, which many developers find very hard to overcome.

SQL Server 2012 introduces some very welcome additions to the existing SSIS product. The most welcome addition, and the one I am most excited about, is the inclusion of a true server-side component to SSIS. Choosing to deploy packages to the server will allow developers and administrators to finally get ease of deployment, and capture the most often requested information about the execution of packages. This server component, called the SSIS Catalog, and its new project deployment model allow administrators to override logging levels, set input parameters, and view built-in reports in an easy-to-use presentation format. In the new project deployment model, the project build process creates a .ispac file, which can be shared with any person doing the physical deployment of the project. The file includes all of the packages in the project, any shared project-level connections, and other metadata pertaining to the project. Double-clicking on the file will start the deployment wizard. Very easy.

Some other changes found in SQL Server 2012 SSIS are a revamped design surface helping to meet accessibility requirements, full undo/redo capability, a removed limit of 4,000 characters on expressions, ability to change variable scopes, and so on.

This book will walk you through, step-by-step, each major feature of SSIS in SQL Server 2012, and how to use them. Pedro and Reza have given contextual examples where possible, and you will be able to download and implement them yourself to help you follow along each recipe. If you are an experienced SSIS developer or you are new to the product, this book will be an often-referenced resource in your bookshelf. Pedro and Reza have put together a great reference book that I know you'll enjoy.

Phil Brammer

Microsoft MVP SQL Server