Professional Microsoft® SQL Server® 2008 Programming

Chapter 16. Getting Integrated

SQL Server Integration Services — or SSIS — is a tool that is a descendant of another tool called Data Transformation Services — or DTS. Remembering DTS is important particularly because of how revolutionary it was at the time it was released (in early 1999 as part of SQL Server 7.0). Never before was a significant tool for moving and transforming large blocks of data included in one of the major Relational Database Management Systems (RDBMSs). All sorts of things that were either very difficult or required very expensive third-party tools were suddenly a relative piece of cake. As we fast forward to the SQL Server 2008 era, what is now called SSIS (the name was changed when the service was totally rewritten as part of SQL Server 2005) is still relatively unique in terms of making such an important tool so accessible.

In this chapter, we'll be looking at how to perform basic import and export of data, and we'll discuss some of the other things possible with tools like Integration Services. We will place our primary focus on the basics of SSIS packages, setting us up for a more advanced discussion of SSIS programmability in the Web-based chapter, Chapter 25.

Understanding the Problem

The problems being addressed by Integration Services exist in at least some form in a large percentage of systems — how to get data into or out of our system from or to foreign data sources. It can be things like importing data from the old system into the new, or a list of ...