Introducing SQL Server 2008 and 2008 R2 Integration Services
January 25, 2010
SQL Server platform comprises of several distinct, highly efficient components, which include Database Engine (with its Service Broker and Replication mechanisms), Analysis Services (incorporating Data Mining and Multi-dimensional Data methodologies), and Reporting Services. All of them are not only highly optimized for the purpose of accomplishing their intended goals, but also capable of interacting with each other. While some of this interaction is inherent to their individual design, its most powerful features are incorporated into the fourth main component, known as SQL Server Integration Services (SSIS). In addition to providing infrastructure for exchanging and manipulating data internal to SQL Server, SSIS also excels in performing equivalent tasks when dealing with wide variety of external data stores. The goal of our article is to describe its general characteristics.
SQL Server Integration Services technology is frequently described using the term Extraction, Transformation, and Loading (ETL), which conveniently summarizes its primary features, although it hardly reflects their breadth and versatility. In particular, the level of flexibility and customization that became available in SQL Server 2008 has been enhanced by tighter integration with Visual Studio, including full support for .NET programming languages (both VB.NET and C#), as well as project-based code management and versioning. At the same rate, however, the need for custom development has been minimized by extending the selection of built-in features that can be employed to deliver desired functionality.
The current version of the product constitutes a major departure from its original implementation introduced in SQL Server 7.0, known as Data Transformation Services (for more information about DTS, refer to our earlier series of articles). The first surge of significant architectural and functional improvements (which you can read about in our comprehensive coverage of this subject on the Database Journal Web site) has been applied to the SQL Server 2005 release, justifying rebranding DTS to its current name. That moment also marked the inception of Business Intelligence Development Studio as the primary tool for managing ETL functionality. Its interface, consistent with the Integrated Development Environment (IDE) of Visual Studio, was a clear indication of the shift in the approach to database design and management, signifying the need for increased programming knowledge among database administrators. While the latest renditions (especially the R2 release) do not bring changes as revolutionary as those characterizing its predecessor, there are numerous innovations worthy of our attention that will be covered in our upcoming articles (one of them is the introduction of Visual Studio Tools for Applications, which further simplifies the Microsoft.NET Framework-based development). More importantly, SSIS further strengthens its position as one of the primary foundations of Business Intelligence, delivering a powerful framework for solutions that combine data from disparate sources, facilitating its analysis and reporting. Focus on the Self-Service aspect of the product empowers end-users, not only simplifying creation of such solutions but also lessening the burden carried commonly by Information Technology departments.
In order to gain an understanding of the principles governing the behavior
of SQL Server Integration Services, we first need to briefly describe its
elementary concepts. As we have already pointed out, the underlying purpose of
this technology is to deliver a combination of data extraction, transformation,
and loading activities. The corresponding work is encapsulated into a software
construct called a
The first of these categories, referred to as
While each task has a unique role in covering a full spectrum of extraction,
transformation, and loading activities,
The dual nature of SSIS packages is reflected by the similarly structured
architecture of underlying system components. More specifically, SSIS divides
its responsibilities between the runtime engine (which implements control flow
functionality with such features as transactional support, debugging, event
handling, or logging), and the data flow engine (which facilitates activities
While each edition of SQL Server 2008 and 2008 R2 is capable of executing
SSIS packages (thanks to the presence of Integration Services runtime), the
level of support for more advanced ETL-related functionality varies. In
particular, from the package authoring perspective, Workgroup, Web, and Express
Editions are limited to the interactive
In our next article, we will take a closer look at the initial setup and configuration of Integration Services in both SQL Server 2008 and 2008 R2 (note that the latter is based on the November 2009 Community Technology Preview).