SQL Server Integration Services is an all-new subsystem for SQL Server 2005. With SQL Server 2005 Microsoft has replaced the old Data Transformation Services (DTS) with the all-new SQL Server Integration Services (SSIS). It’s important to understand that SSIS isn’t a reworked version of DTS. Instead, Microsoft rewrote SSIS from the ground up. Microsoft’s goal for SQL Server 2005’s Integration Services was to make it an enterprise ETL platform for Windows on a par with any of the stand-alone enterprise-level ETL products. The new SSIS is completely redesigned and built using managed .NET code, giving it a more robust foundation.

The new SSIS features a new graphical designer, a greatly enhanced selection of data transfer tasks, better support for programmability and improved run-time. In this chapter you’ll learn how to develop data integration packages using SSIS. First, this chapter will start off by giving you an overview of the new SSIS. Next, you’ll learn about how to create and deploy packages using the SSIS Designer. Then this chapter will wrap up by showing you how you can create and run SSIS packages programmatically using the Microsoft.SqlServer.Dts namespace.

The new Integration Services architecture is divided into two main sections: the Data Transformation Pipeline (DTP) and the Data Transformation Runtime (DTR). The split is designed to make a clear delineation between data flow and control flow. In the previous versions of DTS, the data flow engine was stronger than the control flow capabilities. This new division essentially makes the control flow portion of SSIS a first-class component on the same level as the data flow component. The new DTP essentially takes the place of the old DTS Data Pump that was used in the SQL Server 7 and 2000. Its primary function is to handle the data flow between the source and target destinations.

The DTR is essentially a job execution environment that controls the control flow that’s used in an SSIS package. Each of these components exposes its own distinct object model that you can program against. In Figure 10-1 you can see an overview of the new SQL Server Integration Services architecture. The new Integration Services DTP and DTR are discussed in more detail in the following sections. More information about the new Integration Services tool set is also presented later in this chapter.

Download