Professional SQL Server® 2008 Integration Services

Chapter 11. Using the Relational Engine

There's an old adage that says when you're holding a hammer, everything else looks like a nail. When you use SSIS to build a solution, make sure that you are using the right tool for every problem you tackle. SSIS will be excellent for some jobs, and SQL Server will shine at other tasks. When used in concert, the combination of the two can be powerful.

This chapter discusses other features in the SQL Server arsenal that can help you build robust and high-performance ETL solutions. The SQL Server relational engine has many features that were designed with data loading in mind, and as such the engine and SSIS form a perfect marriage to extract, load, and transform your data. In SQL Server 2008 some of the new relational features were built in direct consultation with the SSIS team.

This chapter assumes you are using SQL Server 2008 as the source system, though many of the same principles will apply to earlier versions of SQL Server and to other relational database systems too. You should also have the SQL Server 2008 versions of AdventureWorks and AdventureWorksDW installed; these are available from www.codeplex.com.

The easiest way to look at how the relational engine can help you design ETL solutions is to segment the topic into the three basic stages of ETL: extraction, transformation, and loading. Because the domain of transformation is mostly within SSIS itself, there is not much to say there about the relational engine, so the scope of interest ...