SSIS consists of four key parts:
Integration Services service
Manages storage of packages and monitors running Integration Services packages.
Integration Services object model
Comprises native and managed APIs for accessing Integration Services tools, command-line utilities, and custom applications.
Integration Services runtime
Saves the layout of packages, runs packages, and supports logging, breakpoints, configuration, connections, and transactions. SSIS runtime executables are the packages, containers, tasks, and event handlers that perform workflow functionality.
Move data from source to destination with optional transformation. There are three types of data-flow componentssource, transformation, and destination (load).
15.1.1. SSIS Objects
The SSIS object model is built on eight primary objects:
A collection of connections, control-flow elements, data-flow elements, event handlers, variables, and configurations either created using SSIS graphical-design tools or built programmatically.
Tasks, containers, and constraints that connect executables into an ordered flow.
Sources and destinations that extract and load the data, data transformations, and paths linking sources, transformations, and destinations. The data flow is created within a data-flow taskan executable that creates, orders, and runs the data flow.
Defines the connection string for accessing data that tasks, transformations, and event handlers in the package use.
A workflow that runs in response to events raised by a package, task, or container.
A set of name-value pairs that defines the properties of the package and its tasks, containers, variables, connections, and event handlers when the package runs. Separating configuration from the package lets you change the properties of the package without changing the package. These objects also facilitate deploying packages from development servers to production servers and moving packages between servers/environments.
Defines the destination type and format used to log runtime information for packages, containers, and tasks.
System and user-defined variables
Stores values that SSIS packages, tasks, and event handlers use at runtime and exposes information about packages at runtime.
Figure 15-1 shows the relationship between the SSIS objects.
The following subsections describe each of these objects in more detail.
15.1.2. Control-Flow Elements
SSIS provides three different types of control-flow elements that can be nested:
Figure 15-1. SSIS object relationship
Provide structures for grouping tasks and implementing repeating control flow in packages. SSIS provides three types of containers :
Foreach Loop container
Repeats control flow for each element in a collection.
For Loop container
Repeats control flow while a test expression evaluates as TRue.
Defines a subset of the control flow in a package. This lets you manage and execute a group of executables (tasks and containers) as a single unit.
Provide functionality within the package. SSIS provides seven types of tasks:
Communicate with other processes to run packages or programs, send and receive messages between packages, send email messages, read Windows Management Instrumentation (WMI) data, and monitor WMI events.
Define and run data flows that extract, transform, and load data.
Copy files and directories, download files and data, save data returned by web methods, and manipulate XML documents.
SQL Server tasks
Access, copy, create, modify, and delete SQL Server data and objects.
Analysis Services tasks
Create, modify, delete, and process Analysis Services objects. Analysis Services is discussed in Chapter 20.
Extend package functionality with custom scripts.
Perform numerous administrative functionsback up the database, check database integrity, execute SQL Service Agent jobs, execute T-SQL statements, clean up history, notify operators, rebuild and reorganize indexes, shrink databases, and update statistics.
Connect containers and tasks within packages in an ordered flow. You can control the sequence of execution and specify conditions that determine whether containers and tasks run.
15.1.3. Data-Flow Components
Data-flow components are connected within a data-flow task using integration service paths. These paths map the outputs of one data-flow component to the inputs of the next data-flow component in the data-flow task. SSIS provides three types of data-flow components:
Retrieve data from an external data source available to components in the data flow. A source has one or more outputs that make source columns available to the next component in the data flow, and can have one or more error outputs. SSIS provides the sources described in Table 15-1. You can develop custom sources if these do not meet your needs.
Table 15-1. SSIS data-flow sources
Data from a .NET Framework data provider
Data from an Excel file
Data from a flat file
Data from an OLE DB provider
Raw data from a file
Data from the results of executing a script
Data from an XML file
Performs tasks such as updating, aggregating, cleaning, distributing, and merging data. A transformation can have single or multiple inputs and outputs depending on the task it performs, and can also have one or more error outputs. SSIS provides business intelligence, row, rowset, split and join transformations, as well as other miscellaneous transformations. You can develop custom transformations if these do not meet your needs. See Microsoft SQL Server 2005 Books Online for more information about the built-in transformations.
Loads data from a data flow into external data sources or creates an in-memory DataSet. Destinations have one or more inputs and optionally one or more error outputs. SSIS provides the destinations described in Table 15-2. You can develop custom destinations if these do not meet your needs.
Table 15-2. SSIS data-flow destinations
Data Mining Model Training
Trains a data-mining model
Exposes data through an ADO.NET DataReader interface
Loads and processes an Analysis Services dimension
Loads an Excel file
Loads a flat file
Loads an OLE DB data destination
Loads and processes an Analysis Services partition
Loads a raw file
Exposes data through an ADO.NET Recordset
Loads data using a script
SQL Server Mobile
Loads a SQL Server Mobile database
SQL Server Destination
Bulk loads data to a SQL Server 2005 table or view
15.1.4. Connection Managers
A connection manager describes the connection to a data source for accessing data that tasks, transformations, and event handlers in the package use. SSIS creates the connections when a package runs. You can define multiple connections for a package. SSIS provides the connection manager types described in Table 15-3.
Table 15-3. SSIS connection manager types
Connects to a data source using ADO
Connects to a data source using the ADO.NET data provider
Connects to an Excel file
Connects to a single file or folder
Connects to data in a single flat file
Connects to an FTP server
Connects to a web service or web site
Connects to a Microsoft Message Queue (MSMQ) queue
Connects to an instance of Analysis Services or to an Analysis Services project
Connects to multiple files and folders
Connects to data in multiple flat files
Connects to a data source using ODBC
Connects to a data source using OLE DB
Connects to SQL Server Management Objects (SMO)
Connects to an SMTP server
Connects to a SQL Server Mobile database
Connects to a WMI server
SSIS executablespackages, Foreach Loop containers, For Loop containers, Sequence containers, and task host containersraise events at runtime. You can write custom event handlers to extend package functionality and simplify administration. If an event does not have a handler, the event bubbles up to the next container in the package hierarchy until it is finally raised to the package. Table 15-4 describes the SSIS runtime events.
Table 15-4. SSIS runtime events
Raised by an executable when an error occurs
Raised by an executable when its execution status changes
Raised by an executable during validation and execution to report information
Raised by an executable immediately after it finishes running
Raised by an executable immediately after it finishes validating
Raised by an executable immediately before it starts running
Raised by an executable immediately before it starts validating
Raised by an executable when progress has been made during execution
Raised by an executable to determine whether it should stop running
Raised by a task when it fails
Raised by an executable when the value of a variable changes
Raised by an executable when a warning occurs
15.1.6. Package Configurations
SSIS provides package configurations for updating property values at runtime. Each package configuration is a property-value pair. Configurations simplify deploying packages to multiple servers, simplify moving packages between servers, and add flexibility to packages by allowing configuration properties to be easily changed. SSIS supports the package-configuration types described in Table 15-5.
Table 15-5. SSIS package-configuration types
XML configuration file
Configuration information is stored in an XML file, which can contain multiple configurations.
Configuration information is stored in an environment variable.
Configuration information is stored in the registry.
Parent package variable
Configuration information is stored in a variable in the package.
SQL Server table
Configuration information is stored in a SQL Server table, which can contain multiple configurations.
15.1.7. Log Providers
SSIS includes log providers that implement logging in packages, containers, and tasks to help you audit and troubleshoot. SSIS offers five log providers, as described in Table 15-6. You can develop custom log providers if these do not meet your needs.
Table 15-6. Log providers
Writes log entries to a text file in comma-separated value (CSV) format
SQL Server Profiler
Writes log entries to SQL Server traces that can be viewed using SQL Server Profiler
Writes log entries to the sysdtslog90 table in a SQL Server 2005 database
Windows Event log
Writes log entries to the Application log in the Windows Event log on the local computer
Writes log entries to an XML file
Variables store values that SSIS packages, tasks, and event handlers use at runtime. SSIS supports two types of variables : user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by SSIS. You can create user-defined variables for all SSIS container typespackages, Foreach Loop containers, For Loop containers, Sequence containers, tasks, and event handlers. Variables are scoped within the scope of a container and are accessible to the children of the container. A variable can raise an event when its value changes.