SQL Server 2005 introduces a new extract transform load (ETL) component, Integration Services. Integration Services replaces Data Transformation Services (DTS), first introduced in SQL Server 7.0 and enhanced in SQL Server 2000. However, Integration Services is not a new and improved version of DTS; instead, Integration Services is redesigned and rebuilt from the ground up. This means a new and very different object model, an expansive application programming interface (API) for programming the object model, and a plethora of graphical tools and wizards to create the packages that comprise an ETL solution. This includes the SQL Server Import and Export Wizard to quickly build a simple package that extracts and loads data; SSIS Designer to create complex packages with multiple inputs and outputs, in-line business intelligence, and data cleaning capabilities; tools to implement logging, configurations, updatable properties, and variables in packages; and finally, the tools to deploy the ETL solution.
In SQL Server 7.0 and SQL Server 2000, the Microsoft Management Console (MMC) snap-in, Enterprise Manager, hosted the tools for SQL Server components, including DTS. In SQL Server 2005, it's goodbye MMC, and hello to the "studio" environment. SQL Server 2005 introduces two studios: Business Intelligence Development Studio and SQL Server Management Studio. Both environments are similar to Microsoft Visual Studio; they include Solution Explorer and Server Explorer views, a Properties window, and windows for debugging. In addition, many of the tools for Integration Services are tightly integrated with the look and feel of the "studio" environment. For example, the windows germane to Integration Services, such as the ones for working with variables or viewing log entries, behave just like the windows that are an intrinsic part of the "studio" environment.
When you are using Integration Services, you are working in both Business Intelligence Development Studio and SQL Server Management Studio. If you are a developer that uses graphical tools to develop business ETL solutions, you can create, debug, and maintain packages in the Business Intelligence Development Environment. If you are an administrator, you can manage packages in SQL Server Management Studio. Either way, you will find that the tools provided by Integration Services go a long way toward addressing the tasks that were just plain difficult to do in the earlier Microsoft ETL offerings. This chapter tells you about these tools.