Chapter 16: Integration Services

Overview

SQL Server Integration Services (SSIS) 2005 is similar to what used to be called Data Transformation Services (or DTS for short) in SQL Server 2000. Like the names suggest, this service is all about integrating data from disparate data sources to a destination with the ability to apply transformations on the data based on business needs. Integration Services is really a collection of utilities, named tasks, and transforms that allow you to merge data from heterogeneous data sources to data destination(s), apply transformations to clean the data before loading them to data destinations, bulk load data to OLTP and OLAP systems, and automate administrative tasks on relational and OLAP databases and even do data mining. Integration Services is not just about data; it helps you to perform operations on files ranging from simple file search operations on disk to transferring files using FTP. It also allows you to write managed code and execute it as a script, and define and automate your complex business processes as nightly tasks. Finally, SSIS allows you to build your own custom tasks and transform components that can be added to the SSIS toolbox.

Integration Services is used extensively to operate on relational databases from data loading to automated administrative tasks. The reason Integration Services is so important to the Business Intelligence professional is simple; data is almost never clean or formatted quite the way you would like and the data almost always comes from heterogeneous data sources. It is imperative to get the data squared away and ready for processing, and Integration Services is one of the ways to accomplish that. Although these things are important parts of the story, they are not the whole story. If there is one thing you have learned working through this book, it is that there are multiple ways to accomplish most any given task. Integration Services adds a whole new operator to that equation! It provides the functionality to do a specific operation via different methods and you can choose the one that is most convenient for you.

Integration Services is based on the formation and use of packages. These packages are made up of connections and tasks; typically built in the user interface, but which could be written directly in XML (though that is not recommended as an approach). These can be reasonably divided into two parts — control flow elements and data flow components. Precedence constraints are what connect tasks in a control flow and define the criteria for progression through the package. Control flow elements take account of cases like; some tasks that fail should result in the termination of the whole package, while failure for another task might mean nothing more than a speed bump and the processing should continue. With data flow such distinctions can be implemented.