Administering SQL Server 2005 Integration Services - Page 4

Logging Packages Execution

The two main types of logging possible with packages in SSIS are

• Logging within the Package—Logging within the package is logging tasks that are built into the package at design time using the SSIS Designer. This form of logging is covered in Chapter 11.

• Console Logging—This form of logging is independent of the package design and is handled by the tool executing the package. This is the type of logging covered in this section.

Console logging shows the progress and status of the package execution. The messages are informative and even give optimization tips to make the package more efficient in addition to just status and error messages.

When running from the execute package utility (the UI for the dtexec utility), the Reporting options specify the level of console logging to do. The default is Verbose, meaning that all events will be displayed.

To capture the console log, use the dtexec utility and pipe the input into a text file. An example is the following command:

dtexec /DTS "\ File System\ PatientStatusImport" /SERVER SQL01
    /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V >
c:\ log.txt

This command executes the package PatientStatusImport from the SSIS file system package store with Verbose console logging and saves the output into the text file c:\ log.txt.

More granular logging requires a bit more effort and forethought during the design of the package. This is covered in Chapter 11.

Backing Up and Restoring Packages

Backing up packages needs to include some additional steps because packages in the SSIS file system store are, in reality, stored in the file system. Even packages stored in the SQL Server, that is, the MSDB, might have some configuration or data files stored in the file system depending on the package.

In addition to the procedures outlined in the Chapter 17, "Backing Up and Restoring the SQL Server 2005 Environment" (online), it is critical to back up the file system as well. This includes the SSIS file system store directory, which by default is %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Packages\ . Backing up this directory backs up all the packages stored in the SSIS file system package store.

Securing Integration Services

SQL Server Integration Services supports a number of security features. These security features protect the packages from unauthorized execution, modification, sensitive information, and even the entire contents of the packages. This section describes the database roles and the protection levels for packages.

In addition to the roles and protection levels, packages can also be signed with certificates to verify the authenticity of the packages. This security feature is examined in Chapter 11.

---

Note - In addition to the security that SSIS provides to packages, you must also be concerned with other areas of security with regards to packages. Packages frequently use data files, configuration files, and log files. These files are not protected by the security mechanisms within SSIS.

---

To ensure that confidential information is not exposed, you must protect the locations of these files as well. Typically, you do this at the operating system level through ACL controls and the Encrypting File System (EFS).

SSIS has three database roles for controlling access to packages. They roughly fall into the categories of administrator, user, and operator. If more granularity is needed in the rights assignment, you can create user-defined roles.

The fixed database level roles and their rights are listed in Table 5.2.

Table 5.2 Fixed Security Roles

Protection levels are set on packages when they are created in the Business Intelligence Development Studio or the wizards. These protection levels prevent the unauthorized execution or modification of packages. Protection levels can be updated on packages when they are imported into the SSIS package store.

The protection levels refer to sensitive information in what they protect or not, which is defined as

• The password part of a connection string is sensitive information.

• The task-generated XML nodes that are tagged as sensitive are considered sensitive information.

• Any variable marked as sensitive is considered sensitive information.

The options for protection levels are listed in the following sections.

Do Not Save Sensitive (DontSaveSensitive)

The DontSaveSensitive option suppresses sensitive information in the package when it is saved. This protection level does not encrypt; instead, it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

Encrypt All with Password (EncryptAllWithPassword)

The SensitiveWithPassword option encrypts the whole package by using a password. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password, the user cannot access or run the package.

Encrypt All with User Key (EncryptAllWithUserKey)

The EncryptAllWithUserKey option encrypts the whole package by using a key based on the user profile. Only the same user using the same profile can load the package. The package is encrypted by using a key that is based on the user who created or exported the package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.

Encrypt Sensitive with Password (EncryptSensitiveWithPassword)

The EncryptSensitiveWithPassword option encrypts only the sensitive information in the package by using a password. Data Protection Application Programming Interface (DPAPI), a standard Windows cryptography component, is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails. For more information about passwords and command-line execution, see dtexec Utility in the SQL Server Books Online.

Encrypt Sensitive with User Key (EncryptSensitiveWithUserKey)

The EncryptSensitiveWithUserKey option is the default setting for packages. It encrypts only the sensitive information in the package by using keys based on the current user. Only the same user using the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.

Rely on Server Storage for Encryption (ServerStorage)

The EncryptSensitiveWithUserKey option protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server MSDB database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.

Command Prompt Utilities

There are two Integration Services–specific command-line utilities. These two utilities allow you to manage, configure, and execute packages from a command line.

Executing Packages with dtexec

The dtexec utility configures and executes SSIS packages from a command line. These packages can be stored in the SSIS package store, a SQL Server, or on a file system. The dtexec utility replaces the dtsrun utility from SQL Server 2000.

The dtexec utility runs in four phases when executing a package:

• Command Sourcing—The command line is parsed.

• Package Load—The package is loaded from the source specified on the command-line arguments.

• Configuration—The command line and package options are processed.

• Validation and Execution—Finally, the package is run or validated.

At the end of the execution, the dtexec utility returns an exit code in the ERRORLEVEL variable for use in batch files or other execution agents. The various exit codes are given in Table 5.3.

Table 5.3 dtexec Utility Exit Codes

These error codes can be used to determine the status of the execution or to branch within the batch file.

The dtexec command line options are

Usage: DTExec /option [value] [/option [value]] ...
Options are case-insensitive.
A hyphen (-) may be used in place of a forward slash (/).
/CheckF[ile]    [Filespec]
/Checkp[ointing]  [{ On | Off} ] (On is the default)
/Com[mandFile]   Filespec
/Conf[igFile]    Filespec
/Conn[ection]    IDOrName;ConnectionString
/Cons[oleLog]    [[DispOpts];[{ E | I} ;List]]
          DispOpts = any one or more of 
          N, C, O, S, G, X, M, or T.
          List = { EventName | SrcName | SrcGuid} [;List]
/De[crypt]     Password
/DT[S]       PackagePath
/F[ile]       Filespec
/H[elp]       [Option]
/L[ogger]      ClassIDOrProgID;ConfigString
/M[axConcurrent]  ConcurrentExecutables
/P[assword]     Password
/Rem[ark]      [Text]
/Rep[orting]    Level[;EventGUIDOrName[;EventGUIDOrName[...]]
          Level = N or V or any one or more of 
          E, W, I, C, D, or P.
/Res[tart]     [{ Deny | Force | IfPossible} ] (Force is the 
default)
/Set        PropertyPath;Value
/Ser[ver]      ServerInstance
/SQ[L]       PackagePath
/Su[m]
/U[ser]       User name
/Va[lidate]
/VerifyB[uild]   Major[;Minor[;Build]]
/VerifyP[ackageid] PackageID
/VerifyS[igned]
/VerifyV[ersionid] VersionID
/W[arnAsError]

Of particular note, the /set option allows you to change the value of any property in the package. This allows the configuration to be adjusted at runtime or variables to be populated. This makes the package execution eminently customizable.

For a detailed description of each option, see the SQL Server Books Online.

---

Note - The dtexec command-line options are processed in the order in which they appear on the command line. Unfortunately, the parser does not report an error if the same option appears twice in the command line.

---

If the same option is configured on the command line, the last specified option is used.

Managing Packages with dtutil

The dtutil utility allows you to manage packages from the command line. It allows access to packages stored in the SSIS package store, SQL Server, and the file system.

With dtutil, you can perform all the tasks that can be performed through the SQL Server Management Server Studio when connected to an Integration Services instance. These tasks include

• Copying packages

• Deleting packages

• Moving packages

• Signing packages

At the end of the execution, the dtutil utility returns an exit code in the ERRORLEVEL variable for use in batch files or other execution agents. The various exit codes are given in Table 5.4.

Table 5.4 dtutil Utility Exit Codes

These error codes can be used to determine the status of the execution or to branch within the batch file.

The dtutil command-line options are

Usage: DTUtil /option [value] [/option [value]] ...
Options are case-insensitive.
A hyphen (-) may be used in place of a forward slash (/).
The vertical bar (|) is the OR operator and is used 
to list possible values.
For extended help use /help with an option. 
For example: DTUtil /help Copy

/C[opy]       { SQL | FILE | DTS} ;Path
/Dec[rypt]     Password
/Del[ete]
/DestP[assword]   Password
/DestS[erver]    Server
/DestU[ser]     User name
/DT[S]       PackagePath
/En[crypt]     { SQL | FILE | DTS} ;Path;ProtectionLevel
[;Password]
/Ex[ists]
/FC[reate]     { SQL | DTS} ;ParentFolderPath;NewFolderName
/FDe[lete]     { SQL | DTS} ;ParentFolderPath;FolderName
/FDi[rectory]    { SQL | DTS} [;FolderPath[;S]]
/FE[xists]     { SQL | DTS} ;FolderPath
/FR[ename]     { SQL | DTS} ;ParentFolderPath;OldFolderName;
NewFolderName
/Fi[le]       Filespec
/H[elp]       [Option]
/I[DRegenerate]
/M[ove]       { SQL | FILE | DTS} ;Path
/Q[uiet]
/R[emark]      [Text]
/Si[gn]       { SQL | FILE | DTS} ;Path;Hash
/SourceP[assword]  Password
/SourceS[erver]   Server
/SourceU[ser]    User name
/SQ[L]       PackagePath

For a detailed description of each option, see the SQL Server Books Online.

Summary

SQL Server 2005 Integration Services provides a set of strong extraction, transformation, and loading (ETL) services that are fast and robust. These services include many features and options for ensuring that data integration is handled effectively. This feature was somewhat lacking in previous versions of SQL Server, but SQL Server 2005 improves on it immensely.

For more detailed information on the creation of packages and numerous options within them, see Chapter 11.

Best Practices

Some important best practices from the chapter include

• Export packages before deleting them.

• Take care when mixing 32-bit and 64-bit versions of SSIS to ensure full interoperability.

• Create user-defined security roles to ensure the least privilege security principle.

Copyright © 2007 Pearson Education. All rights reserved.