Professional SQL Server™ Analysis Services 2005 with MDX

Chapter 16. Integration Services

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 ...