Section 15.1. Architecture


15.1. Architecture

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.


Data flows

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:


Package

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.


Control flow

Tasks, containers, and constraints that connect executables into an ordered flow.


Data 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.


Connection manager

Defines the connection string for accessing data that tasks, transformations, and event handlers in the package use.


Event handler

A workflow that runs in response to events raised by a package, task, or container.


Configuration

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.


Log provider

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



Containers

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.


Sequence container

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.


Tasks

Provide functionality within the package. SSIS provides seven types of tasks:


Workflow 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.


Data-flow tasks

Define and run data flows that extract, transform, and load data.


Data-preparation tasks

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.


Scripting tasks

Extend package functionality with custom scripts.


Maintenance tasks

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.


Precedent constraints

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:


Source

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

Source

Description

DataReader

Data from a .NET Framework data provider

Excel

Data from an Excel file

Flat File

Data from a flat file

OLE DB

Data from an OLE DB provider

Raw File

Raw data from a file

Script Component

Data from the results of executing a script

XML

Data from an XML file



Transformation

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.


Destination

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

Destination

Description

Data Mining Model Training

Trains a data-mining model

DataReader

Exposes data through an ADO.NET DataReader interface

Dimension Processing

Loads and processes an Analysis Services dimension

Excel

Loads an Excel file

Flat File

Loads a flat file

OLE DB

Loads an OLE DB data destination

Partition Processing

Loads and processes an Analysis Services partition

Raw File

Loads a raw file

Recordset

Exposes data through an ADO.NET Recordset

Script Component

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

Type

Description

ADO

Connects to a data source using ADO

ADO.NET

Connects to a data source using the ADO.NET data provider

EXCEL

Connects to an Excel file

FILE

Connects to a single file or folder

FLATFILE

Connects to data in a single flat file

FTP

Connects to an FTP server

HTTP

Connects to a web service or web site

MSMQ

Connects to a Microsoft Message Queue (MSMQ) queue

MSOLAP90

Connects to an instance of Analysis Services or to an Analysis Services project

MULTIFILE

Connects to multiple files and folders

MULTIFLATFILE

Connects to data in multiple flat files

ODBC

Connects to a data source using ODBC

OLEDB

Connects to a data source using OLE DB

SMOServer

Connects to SQL Server Management Objects (SMO)

SMTP

Connects to an SMTP server

SQLMOBILE

Connects to a SQL Server Mobile database

WMI

Connects to a WMI server


15.1.5. Events

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

Event

Description

OnError

Raised by an executable when an error occurs

OnExecStatusChanged

Raised by an executable when its execution status changes

OnInformation

Raised by an executable during validation and execution to report information

OnPostExecute

Raised by an executable immediately after it finishes running

OnPostValidate

Raised by an executable immediately after it finishes validating

OnPreExecute

Raised by an executable immediately before it starts running

OnPreValidate

Raised by an executable immediately before it starts validating

OnProgress

Raised by an executable when progress has been made during execution

OnQueryCancel

Raised by an executable to determine whether it should stop running

OnTaskFailed

Raised by a task when it fails

OnVariableValueChanged

Raised by an executable when the value of a variable changes

OnWarning

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

Type

Description

XML configuration file

Configuration information is stored in an XML file, which can contain multiple configurations.

Environment variable

Configuration information is stored in an environment variable.

Registry entry

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

Provider

ProgID

Description

Text file

DTS.LogProviderTextFile.1

Writes log entries to a text file in comma-separated value (CSV) format

SQL Server Profiler

DTS.LogProviderSQLProfiler.1

Writes log entries to SQL Server traces that can be viewed using SQL Server Profiler

SQL Server

DTS.LogProviderSQLServer.1

Writes log entries to the sysdtslog90 table in a SQL Server 2005 database

Windows Event log

DTS.LogProviderEventLog.1

Writes log entries to the Application log in the Windows Event log on the local computer

XML file

DTS.LogProviderXMLFile.1

Writes log entries to an XML file


15.1.8. Variables

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.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net