With DTS in SQL Server 2000, control flow and data flow were tightly coupled, making it difficult to create and manage complex packages. The new SSIS architecture breaks this tight coupling and separates control flow from data flow. You will see this change when you start designing packages in SSIS designer inside Business Intelligence Development Studio. Control Flow and Data Flow are two tabs or screens in the SSIS package designer. The other change in the SSIS architecture is the division of package creation, storage, execution, and data flow into four core components: clients, the SSIS service, the runtime engine, and data flow engine.
Before further exploring these four core components, here are some SSIS terms that you should become familiar with:
Figure 12.1. An SSIS package is a collection of components such as tasks, containers, connections, variables, and configurations.
The following sections look at the core SSIS components in more detail.
The SSIS clients include the built-in tools, wizards, and command-line utilities, as well the custom applications that you create for designing and creation of packages. The SSIS designer graphical tool allows you to create SSIS packages without writing a single line of code. The designer contains a collection of built-in tasks to choose from, or you can program a custom task by using .NET managed or native code. In addition to the designer, SSIS contains several wizards and command prompt utilities that assist in configuring the packages by creating configuration files, deploying packages, and creating package dependencies. These are discussed later in this chapter.
SSIS has separated the management and design of packages for better manageability into a Windows service (MsDtsSrvr.exe). This service provides management support for SSIS package storage and execution. This service is off by default, and the startup type is set as disabled but is automatically started when the first DTS package runs. You can manage this service by using the SQL Server Configuration Manager tool. In addition to monitoring local and remote running packages, this service also controls the storage of packages. Packages can be stored either in the sysdtspackages90 table in the msdb SQL Server system database or as XML files (.dtsx) on the file system.
Business Intelligence Development Studio allows you to save SSIS packages to disk files only as .dtsx XML-formatted files. You can export the packages to the msdb database by using SQL Server Management Studio.
The SSIS Runtime Engine
The DTS runtime engine executes packages and provides support for logging, debugging, configuration, connections, and transactions. It provides support for the following:
The runtime engine is also responsible for establishing the connection to any external data source used by the package.
The Data Flow Engine
The SSIS data flow engine manages the tasks that move data between the source and destination. SSIS uses in-memory buffers for better performance. The Data Flow task provides and manages these buffers, calls the appropriate data flow components, and provides transformations to modify and cleanse data. The Data Flow task is a set of connected data flow components consisting of data adapters and pipelines. These are the different data flow components (see Figure 12.2):
Figure 12.2. The Data Flow task is a set of connected data flow components consisting of source adapters, transforms, destination adapters, and pipelines.
As shown in Figure 12.2, the data flow task can contain one or more source adapters to read the data, one or more transformations to process and cleanse the data, and one or more destinations to store the data.
The data flow engine manages interdependency between the different components of a data flow task and also supports parallel execution of the non-connected tasks. The data flow engine supports merging, splitting, and multicasting of data in a data flow and can also be used to redirect an error to a destination output.