Microsoft SQL Server 2005 New Features [Electronic resources] نسخه متنی

Integration Services Package Components

Integration Services packages are organized collections of DTP and DTR components. The package is the unit of execution for a Integration Services transfer operation. In other words, to use Integration Services to perform data transfers and ETL operations, you must create a package that contains all of the DTP components that define the source target and destinations for the data and the transformations that will take place, as well as the DTR components that define the work flow or sequence of actions that will be performed by the Integration Services package. Once the package has been created, you execute the package to perform the data transfer. You can see an overview of a sample Integration Services package shown in Figure 9-4.

Figure 9-4: Integration Services package overview

Integration Services packages can be created using the set of tools provided with SQL Server 2005, or they can be created programmatically using the DTR API. Integration Services packages can be saved either in SQL Server’s msdb database or as XML files that reside in the file systems. You can get a preview of the Integration Services design tools in the section “Integration Services Tools” that is presented later in this chapter.

Integration Services Package Features

Not surprisingly, the new Integration Services architecture adds some significant new capabilities to Integration Services that make it a more robust enterprise data transformation tool. Next, let’s look at some of the major enhancements that this new version of Integration Services brings to the table.

Setting Run-Time Properties of Integration Services Packages

One of the major limitations of the earlier versions of Integration Services was the fact that packages were essentially tied to a given source and destination. That meant that it was really difficult to take a given package and easily reuse it on a different server, even if that server supported the same databases. While it was possible to use global variables and other complex workarounds to get around this limitation, these workarounds weren’t the most graceful or robust solutions to the problem. The new SQL Server 2005 Integration Services enables one package to handle multiple source and destination endpoints. You can use this feature to set package properties at run time from parameters files, the Registry, or XML documents.

Logging

The original Integration Services also didn’t support any built-in logging capabilities. Logging the operation of packages is particularly useful for auditing and troubleshooting. While you could add them yourself, this required development work on your part. The new SQL Server 2005 Integration Services provides built-in logging options for packages, tasks, and transformations.

Checkpoint Restart

Another great new feature in SQL Server 2005’s Integration Services is support for checkpoints and restarts within Integration Services packages. This new feature enables different checkpoints to be associated with multiple steps in complex Integration Services packages. Thus if a package fails that’s using checkpoints, the entire package doesn’t need to be rerun from the very beginning. Instead, the package can be restarted at the first unsuccessful checkpoint. For long-running packages, this can be a huge time saver, as the entire package doesn’t need to be reprocessed. Instead, the Integration Services package can resume processing where it left off. When combined with event handling, this can be a powerful tool for automatically managing your Integration Services jobs.

Variables

Support for variables is another new feature found in SQL Server 2005 Integration Services. With SQL Server 7 and SQL Server 2000, DTS packages supported variables for use in transformation scripts but didn’t support package-scoped variables. This lack of global variables made it difficult to reuse DTS packages on multiple databases and on multiple servers. To be sure, there were workarounds, but since DTS packages were not designed to be used in this fashion, these workarounds were somewhat complex and not particularly robust. With SQL Server 2005 the new Integration Services design provides support for package, task, and event-level variables, greatly increasing the flexibility of Integration Services packages and also significantly facilitating the reuse of Integration Services packages. As their name implies, package-scoped variables can be seen by all of the components in the Integration Services package. Task-scoped variables can be seen and accessed by the components of that particular task but not by any other components that are part of that Integration Services package. One example usage of a package-scoped Integration Services variable might be to contain the name of the current SQL Server system, allowing the package to be easily run on another system by substituting that system’s server name into the Integration Services variable at run time.

System Variables

All Integration Services packages have a number of built-in system variables that represent different aspects of a Integration Services package. Table 9-1 lists the new Integration Services System variables.

Complex Flow Control

SQL Server 2005’s Integration Services also supports complex flow control. Within each Integration Services package you can specify the path that will be taken if a given operation succeeds or fails. For instance, if an operation succeeds you can set up your Integration Services package to proceed along with the next subsequent action. Otherwise, if the operation fails you can take an alternative action. More information about the new error flow feature is presented in the following section. You can also set up the control flow in such a way that multiple tasks can be executed in parallel, or you can force tasks to execute sequentially by specifying that the next task will not be executed until the current task has completed. You can also use a new Integration Services construct known as a container to group together related Integration Services tasks. Each container can have its own internal control flow and variables. There are also multiple looping structures that enable you to set up repeated actions. There’s a For Each Loop container that is capable of iterating over a group of objects, taking action on those objects on every iterations. In addition, there’s a For Loop container that can evaluate an expression and conditionally perform repeated actions. More information about all of the available tasks is presented later in this chapter.

Error Flows

One of the big new features with SQL Server 2005 Integration Services is its ability to support error flows. The new Error Flows feature essentially enables you to add error handling to your Integration Services packages. With the new Error flows feature, when a Integration Services transform encounters rows that produce error conditions, instead of halting the process with an error, Integration Services can route the problem row according to the error flow that has been set up. For instance, the error flow may indicate that the row is simply written out to a log file, or it can also direct the package into a task that performs much more sophisticated error routines that can even manipulate the data and place the row back into the pipeline for reprocessing.

Immediate Mode and Project Mode

Similar to the way that SQL Server 2000 DTS worked, where DTS had both a wizard interface that was primarily designed to execute ad hoc data transfers and a DTS Designer that was used to build more complex DTS packages, SQL Server 2005’s Integration Services supports both an immediate mode and a project mode. To use Integration Services in the immediate mode, you can run the Integration Services Import/Export Wizard from the menu. The Integration Services Import/Export Wizard can be used to build, execute, and optionally save Integration Services packages that perform simple transfers. SQL Server 2005’s new Integration Services Import/Export Wizard looks and acts much like the version found in SQL Server 2000.

While immediate mode is useful for quick one-time data transfer operations, the project mode is useful for building more sophisticated Integration Services packages using the Business Intelligence Development Studio. The Business Intelligence Development Studio contains an all-new Integration Services Designer that supports an entirely new set of Data Flow, Control Flow, and Event handlers that can be used to build Integration Services packages. The new Integration Services Designer also provides full support for debugging Integration Services packages. You can see the new Integration Services Designer in more detail in the section “Integration Services Tools” later in this chapter.

Digital Signing of Integration Services Packages

Using a method much like the digital signing feature found that’s available for Microsoft .NET applications, Integration Services packages can now be signed as well. This enables you to verify the authenticity of a package when it is executed. Packages can be digitally signed during the design process using the Integration Services Designer. Once a package had been digitally signed, that package is read-only and can no longer be modified.

Data Adapters

The DTP uses data adapters to connect data source and destination endpoints. As their names suggest, source data adapters connect to the source of the data and provide the input for Integration Services packages. Destination data adapters connect to the data target and output the data. SQL Server 2005’s provides a number of source and destination data adapters. Table 9-2 lists the set of built-in data adapters that are supplied with SQL Server 2005.

Containers

Containers are a new construct that Microsoft has added to SQL Server 2005’s Integration Services. The primary purpose for Integration Services containers is to add structure and flow control to your Integration Services packages. Containers group together related tasks and are designed to be used to execute repeated tasks or to provide scope for variables. SQL Server 2005 Integration Services supports the types of containers shown in Table 9-3.

Tasks

The Integration Services tasks are the most basic elements of the Integration Services package. The Integration Services task essentially defines an action that will be performed. These actions range from copying files, executing T-SQL statements, and running scripts, to performing FTP transfers and running data mining models. Multiple related tasks can be grouped into containers. Table 9-4 presents the tasks that are included in SQL Server 2005’s Integration Services.

Transformations

Integration Services transformations control what happens to the data as it’s moved from the source data adapter to the destination data adapter. SQL Server 2005 supports both a number of built-in transformations and user-defined custom transformations. You can create custom transformations using the API provided by the DTP object model. SQL Server 2005’s Integration Services provides an extensive list of built-in standard transformations, as shown in Table 9-5.

Event Handling

The ability to raise and handle events is another new feature found SQL Server 2005 Integration Services. Event handling enables Integration Services packages to respond to events that are raised at run time by containers and tasks. Events can be fired by the Integration Services package elements to signal a number of different states, including error conditions, when a task has started, when a task completes, or a change in variable status. Table 9-6 lists the Integration Services Event Handlers.

Log Providers

Log providers are another completely new feature that Microsoft has added to Integration Services packages. As their name suggests, Integration Services log providers enable you to add logging to your packages, containers, and tasks. Logging is used to record error information or other important run-time or status information. Table 9-7 lists the log providers that Microsoft ships with SQL Server 2005.