Administering SQL Server 2005 Integration Services

Wednesday Oct 31st 2007 by DatabaseJournal.com Staff
Share:

This excerpt from "SQL Server 2005 Management and Administration" covers What's new in Integration Services with Service Pack 2, understanding, managing and administering Integration Services and command prompt utilities.

SQL Server 2005 Management and Administration
By Ross Mistry, Chris Amaris, Alec Minty, and Rand Morimoto
Published by Sams Publishing
ISBN-10: 0-672-32956-5
ISBN-13: 978-0-672-32956-2
Buy this book

Chapter 5: Administering SQL Server 2005 Integration Services

SQL Server 2005 Integration Services (SSIS) replaces the SQL Server 2000 Data Transformation Services (DTS). SSIS provides for the following data services:

  • Extraction

  • Transformation

  • Loading

This triumvirate of data services is frequently referred to as ETL. The process encapsulates the extraction of data from a source, the transformation of the data to suit the requirements of the application, and finally the load of the data into a destination. The transformations can include normalizing, sanitizing, merging, aggregating, and copying the data. The sources and destinations for the data can be SQL databases, third-party ODBC data, flat files, or any number of other data locations.

SSIS delivers high-performance ETL services with a rich set of tools for designing, testing, executing, and monitoring these integrations. The subject of SSIS is broken down into administration and development. This chapter addresses the topic of SSIS administration, and Chapter 11, "Creating Packages and Transferring Data," covers development.

What's New in Integration Services with Service Pack 2

There are a number of new features and enhancements to SSIS in SQL Server 2005 Service Pack 2. The principal changes are

  • SQL Server Import and Export Wizard supports Office 12, providing connectivity to Access 2007 and Excel 2007.

  • The package's interaction with external data sources is now logged for troubleshooting.

  • Data flow components now support combo boxes for variables.

  • The Execute SQL task now has a ParameterSize property for string values.

  • The IsNull property of columns in the Script Component raises a warning when it is used incorrectly.

  • The Lookup transformation reports the final count of cached rows.

In addition, the overall performance of SSIS has been improved in Service Pack 2. This allows faster package execution and higher data throughput.

Understanding Integration Services

SSIS is fundamentally a service for storing and executing packages. Administration and management are covered in this chapter; this principally is the administration and management of package storage and execution.

Much of the complexity in integrating disparate data stores is pushed down into the packages themselves. This level of detail is examined in Chapter 11.

Integration Services Object Model

The Integration Services Object Model is the collection of objects and the interactions between them. Objects within SSIS are

  • Packages

  • Control Flows

  • Data Flows

  • Connections

  • Variables

  • Event Handlers

  • Log Providers

For the administration of SSIS, the main consideration is packages. The package object contains all the preceding objects and provides the most granular level of SSIS administration. The package is almost an atomic entity from the perspective of Integration Services.

Packages

Packages are the core structure for Integration Services. They contain the logic that defines how the data will be integrated. They contain control flows (that is, control tasks) and data flows (that is, data tasks). When packages are executed, the control and data flows are executed.

Packages can be stored in SQL Server 2005, to the Integration Services package store, or to a file system.

The internal objects within packages, ways to create packages in Business Intelligence Development Studio, and various options within packages are covered in Chapter 11.

Projects and Solutions

Packages are organized into solutions and projects, which are, respectively, containers used to facilitate the development of packages and groups of packages that accomplish a business purpose.

Projects are containers for the packages, and a single project can contain multiple packages. Solutions are containers for projects, and a single solution can contain multiple projects. The solutions, projects, and packages are created, tested, and deployed from SQL Server 2005 Business Intelligence Development Studio.

Although solutions and projects are used to organize the packages, ultimately, the packages are what SSIS uses to actually do work. These units are executed to actually manipulate data. Integration Services fundamentally does not interact with the overarching solution and project organizing containers.

This chapter does not address the creation of solutions and projects. This topic is covered in Chapter 11.

Integration Services Service

Integration Services is a Windows service that manages SSIS packages. The IS service handles the following:

  • Starting and stopping packages

  • Importing and exporting packages

  • Managing the SSIS Package Storage

  • Providing integration with SQL Server Management Studio

The service is not required for the design and running of packages, but it facilitates the monitoring of packages in the SSIS package store in the SQL Server Management Studio.

If the service is stopped, packages can still be executed using other tools such as the execute package utility, SSIS Designer, SQL Server Import and Export Wizard, or dtexec command-line utility.

64-Bit Limitations of Integration Services

Not all features of SSIS are available in 64-bit versions, so you need to take care with some issues. And there are further limitations when comparing AMD and Itanium 64-bit platforms.


Note - This problem is not unique to SSIS. Many other applications have limitations and provisos when you're installing on 64-bit platforms and Itanium 64-bit platforms.


SQL Server 2005 64-bit installs all the possible design and run components of SSIS, placing the 32-bit-only versions in "Program Files (x86)" and the 64-bit versions in "Program Files."

Some of the compatibility issues and limitations of the 64-bit version of SSIS include

  • Business Intelligence Development Studio is not supported on Itanium 64-bit. This component is not supported on Itanium 64-bit computers and is not installed on this platform. It is installed and supported on AMD 64-bit computers.

  • A limited number of native 64-bit tools is supported. The only native 64-bit SSIS tools are dtexec, dtutil, and the SQL Server Import and Export Wizard. All the other tools are 32-bit versions when SSIS is installed on 64-bit servers.

  • The execute package utility is 32-bit. The UI version of the dtexec utility that runs packages from within SQL Server Management Studio, the dtexecui utility, is 32-bit and runs packages in 32-bit mode.

  • The SQL Server message queue task is either 64-bit or 32-bit. The Message Queue task only runs in packages that are running in the same mode as the SQL Server installation. If a 64-bit version of SQL is installed, then the Message Queue task only runs in packages that are running in 64-bit mode. The Message Queue task does not run using the execute package utility, which is only 32-bit.

  • The SQL Server Agent is only 64-bit. The SQL Server Agent runs in 64-bit mode on 64-bit platforms, so jobs with SSIS package-execution-type steps use the 64-bit dtexec utility and run in 64-bit mode. To run packages in 32-bit mode, you must use the operating-system-type step and use the 32-bit version of the dtexec in the command line.

  • SSIS is not backward compatible with DTS jobs in 64-bit. The 64-bit SQL Server 2005 does not support any backward compatibility with DTS packages created in earlier versions of SQL.

When using a mixed environment of 64-bit and 32-bit SQL Server 2005 Integration Services, you must take care to ensure that compatibility issues do not arise.

There are also some package development issues that need to be taken into account; they're covered in Chapter 11.

Managing Integration Services

Much of the management of Integration Services revolves around packages. The tasks that you need to perform as database administrator are essentially package tasks, including creating, storing, and running packages, and other package-specific tasks.

Creating an Integration Services Package

Using the SQL Server Import and Export Wizard is one of the easiest ways to create a basic package. Sophisticated package creation is done through the Business Intelligence Development Studio (BIDS).

This example creates a package that will import comma-delimited data from biometric monitors for patients in a vaccination study. The biometric data consists of a patient number and a series of measurements of the patient's temperature, pulse, respiration, and blood pressure.

The blank database named BioData already exists on the SQL Server 2005 server named SQL01. The biometric data will be refreshed periodically, so you need a package to import the data for ease of scheduling. To create it, follow these steps:

  1. Open SQL Server Management Studio.

  2. Connect to the Database Engine of the SQL01 SQL server.

  3. Expand the Databases folder in Object Explorer.

  4. Right-click on the BioData database.

  5. Select Tasks, Import Data.

  6. Click Next.

  7. From the Data source drop-down, select Flat File Source.

  8. Enter the filename of the source data, in this case biodata.txt.

  9. A warning appears in the messages window, stating "Columns are not defined for this connection manager."

  10. Check the box Column Names in the first data row.

  11. Click Next.

  12. Click Next to leave the destination defaults.

  13. Click Edit Mappings.

  14. Check the box Drop and Re-create Destination Table. This setting overwrites the table each time the package is run.

  15. Click OK.

  16. Click Next.

  17. Check the box Save SSIS Package and click Next. This saves the package to the SQL Server.

  18. Enter a name for the package, in this case BioDataImport.

  19. Click Next and then Finish.

The package execution results are summarized in a window, as shown in Figure 5.1. Note that the package completed with one error, which was due to the BioData table not existing on the first run. As you can see from the figure, more than 7 million rows were imported into the table.

The package was installed to the SQL Server. To confirm this, you can connect the Integration Services instance using the following steps:

  1. In SQL Server Management Studio Object Explorer, click on the Connect drop-down and select Integration Services.

  2. Click Connect to connect with the current credentials.

  3. Expand the Stored Packages folder in the Object Explorer window.

  4. Expand the MSDB folder to see the BioDataImport package.

At this point, you should see the package in the folder.

Figure 5.1
Using the SQL Server Import and Export Wizard.

Storing Packages

Packages can be stored in the SQL Server MSDB database, SSIS package storage, or file system. Only the SQL Server storage and SSIS file system storage are managed by SSIS.

Folders and subfolders can be created in either the MSDB storage or the SSIS file system storage to help organize the packages.

Packages stored in the MSDB database are stored in the sysdtspackages90 table. Folders within the MSDB are stored as rows within the database.

Packages stored in the SSIS file system storage are stored by default in the %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Packages\ directory. Packages can be viewed and managed in these folders from the Object Explorer within SSIS, as well as the file system. Folders created in the file system are actually file folders in the directory structure.


Note - Unfortunately, packages cannot be dragged and dropped within the folder structure of the SSIS storage. You must use the export and import feature to move the packages around.


However, you can drag and drop files within the native Windows file system. When you do, the changes are reflected in the SSIS file system folder.

Importing and Exporting Packages

Packages can be imported and exported from the SSIS storage. This can be done with the Object Explorer in SQL Server Management Studio or the file system in Windows.

Packages can be exported to

  • Back up the packages

  • Move the packages to a different SQL Server

  • Include the packages in a project or solution using SQL Server Business Intelligence Development Studio

Packages can be exported to SQL Server (that is, the MSDB database), the SSIS package store, or the file system. The destination can be local or on another server, such as another SQL Server or even just a file share on another server. The saved file will have a .dtsx extension.

To export a package, such as the BioDataImport package, from SSIS to a backup file, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the MSDB folder.

  6. Right-click on a package to export, in this case the BioDataImport package.

  7. Select Export Package.

  8. Select the destination of the package, in this case File System.

  9. Enter the path and filename for the package. The path must exist already for the package to be saved.

  10. Alternatively, click on the button next to the Package path field to browse for a location.

  11. Click OK to save the package.

These steps save the package in XML format, which you can then import into another server with SQL Server Management Studio or into a project in the Business Intelligence Development Studio.

To import a package in SQL Server Management Studio, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Right-click on the File System folder.

  6. Select Import Package.

  7. Select the source of the package, in this case File System.

  8. Enter the path and filename for the package. Alternatively, click on the button next to the Package path field to browse for a package. In this case, the package named PatientStatusImport.dtsx is imported to load patient status. This package imports data from a file named PatientStatus.txt.

  9. Click in the Package Name field. The package name is filled in automatically but can be changed if needed.

  10. Click OK to save the package.

The package is then displayed in the File System folder. You can now run the package from there.

Running Packages

You can trigger the packages from within the SQL Server Management Studio and monitor their execution progress in detail. The contents of packages cannot be reviewed within the tool. To do that, you must export the package and then open it in the Business Intelligence Development Studio (see Chapter 11).

To run a package (using the newly imported PatientStatusImport package) within SSIS, do the following:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the File System folder.

  6. Right-click the PatientStatusImport package imported earlier and select Run Package.

  7. The execute package utility runs.

  8. In the General options page, there is the package source, the server, the authentication, and the package name.

  9. Click the Reporting options page to see the reporting options available.

  10. Click the Command Line options page to see the command-line version of the execution. This capability is useful to automate the package execution in the future.


Note - You can add parameters to the command line by selecting the Edit the Command Line Manually option.


  1. Click Execute to run the package.

  2. The Package Execution Progress window opens, displaying the package progress and information as shown in Figure 5.2. The message indicated shows that 10,000 rows were written to the table.

Figure 5.2
Package Execution Progress window.

  1. Click Close to close the progress window.

  2. Click Close to close the execute package utility.

The execution of the package logs a pair of events in the Windows NT Application event log. These events are from source SQLISPackage with event ID 12288 when the package starts and 12289 when the package finishes successfully. If the package fails, the event ID logged is 12291.

Scheduling Packages

Packages can be scheduled to run automatically using the SQL Server Agent. The packages need to be stored in the SSIS package store, either the MSDB or the file system, to be scheduled.

In the example, the Patient Status Data package needs to be run every day at 6 a.m. to update the patient status. To schedule a package for execution, follow these steps:

  1. Open SQL Server Management Studio.

  2. Connect to the Database Engine of the SQL Server.

  3. Right-click on SQL Server Agent and select New, Job.

  4. In the General options page, enter the name of the job, in this example Daily Patient Status Update.

  5. Select the Steps option page.

  6. Click New to create a new step.

  7. Enter the Step name, in the example Update Patient Status.

  8. In the Type pull-down, select SQL Server Integration Services Package.

  9. In the Package Source pull-down, select the SSIS package store.

  10. In the Server pull-down, select the server name.

  11. Click on the Package selection button to the right of the Package field.

  12. Browse the Select an SSIS Package window to find the package, in this example the PatientStatusImport package imported earlier.

  13. Click OK.

  14. Click OK to save the step.

  15. Select the Schedules option page.

  16. Click New to create a new job schedule.

  17. In the Name field, enter Daily at 6am.

  18. In the Occurs pull-down, select Daily.

  19. Change the Daily Frequency to 6:00:00 AM.

  20. Click OK to save the schedule.

  21. Click OK to save the job.

The job will now run the SSIS package at 6 a.m. every day. The job is saved in the database and can be reviewed in the Jobs folder within the SQL Server Agent. You can test it by right-clicking on the job and selecting Start Job.

Jobs can run a series of packages in a sequence of steps and even with conditional branches that depend on the output of the preceding packages. This allows packages to be chained together to complete a larger task.

Managing the Running Packages

You can view the running packages in the SQL Server Management Studio connection to Integration Services. The list of running packages is located under the Running Packages folder.

To generate a report of the running package, right-click on the running package and select General. The report, shown in Figure 5.3, is not very detailed. The main information is in the Execution Started and Executed By fields.

Figure 5.3
Running package.

You also can stop the package from the Integration Services Object Explorer Running Packages window. Simply right-click on the running package and select Stop.

Unfortunately, the management of running packages within the SQL Server Management Studio is limited and the information presented minimal. The tool gives only a general sense of what the packages are doing, rather than the details you might be looking for.

Removing Packages

To remove the packages from the Integration Services package store, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the File System folder.

  6. Right-click the PatientStatusImport package imported earlier and select Delete.

  7. Click on Yes to confirm the deletion of the package.

When a package is deleted, it cannot be retrieved except by restoring from a backup. There is no "undo" function within the tool, so delete packages carefully and only after exporting the packages for safekeeping.

Administering Integration Services

Administering the SQL Server 2005 Integration Services consists of administering the Integration Services service and packages. There are relatively few administration activities directly related to SSIS itself because it is mainly a vehicle for running and storing packages.

Administering the Integration Services Service

The default behavior of the Integration Services service can be modified to suit the needs of the application. Three options can be configured for the Integration Services service:

  • Stopping packages when the service stops

  • Changing the File System folder default location

  • Changing the default root folders to display

The configuration file, named MsDtsSrvr.ini,xml, is located in %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Binn\ . The configuration file is loaded when the service starts.

The default configuration file for the Integration Services service is

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd=http://www.w3.org/2001/XMLSchema
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true
 </StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  </Folder>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\ Packages</StorePath>
  </Folder>
 </TopLevelFolders>
</DtsServiceConfiguration>

You can change the service configuration simply by modifying the configuration file and then restarting the service.

For example, if you want to configure the service not to stop packages on shutdown and to add the SQL Server folder (MSDB database) to the Object Explorer view, your modified configuration file would look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd=http://www.w3.org/2001/XMLSchema
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>false
 </StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  </Folder>
  <Folder xsi:type="SqlServerFolder">
   <Name>SQL02MSDB</Name>
   <ServerName>SQL02</ServerName>
  </Folder>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\ Packages</StorePath>
  </Folder>
 </TopLevelFolders>
</DtsServiceConfiguration>

This example shows how the file would be modified for a SQL Server named SQL01 to be able to explore the packages in the MSDB on SQL02. After you modified the configuration file, there would be an additional root folder SQL02MSDB showing packages stored in the SQL Server Store of SQL02. And packages running on SQL01 would not stop when the service was stopped.

Monitoring Package Execution

Integration Services exposes a number of performance counters that allow the monitoring of package execution in real-time or the capture for historical reference. This shows the number of rows being processed, the memory being used in different capacities, and the impact of certain data types on the package performance. The counters can be used to tune the performance of the package and see the impact of the changes.

A couple of terms used in the counters bear explanation. Flat buffers are blocks of memory that a component uses to store data. A flat buffer is a large block of bytes that is accessed byte by byte. Binary large objects (BLOBs) are data types used in SQL to store large binary data such as images or unstructured text. They can be large objects and can affect the performance of a package, so understanding how much data is from this type is important.

The object is the SQLServer:SSIS Pipeline, and the various counters are listed in Table 5.1.

Table 5.1 Integration Services Package Counters

Performance Counter

Description

Rows read

The number of rows that a source produces. This counter is for the life of the SSIS instance and resets if the service is restarted.

Rows written

The number of rows offered to a destination. This counter is for the life of the SSIS instance and resets if the service is restarted.

Buffer memory

The amount of memory in use.

Buffers in use

The number of buffer objects that all data flow components and the data flow engine are currently using.

Buffers spooled

The number of buffers currently written to the disk.

Private buffer memory

The total amount of memory in use by all private buffers.

Private buffers in use

The number of buffers that transformations use.

BLOB bytes read

The number of bytes of BLOB data that the data flow engine has read from all sources.

BLOB bytes written

The number of bytes of BLOB data that the data flow engine has written to all destinations.

BLOB files in use

The number of BLOB files that the data flow engine currently is using for spooling.

Flat buffer memory

The total amount of memory, in bytes, that all flat buffers use.

Flat buffers in use

The number of flat buffers that the data flow engine uses. All flat buffers are private buffers.

The counters can be used directly from the Performance Microsoft Management Console (MMC) for a real-time view of the package performance or can be used with a monitoring tool such as Microsoft Operations Manager (MOM) 2007.

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

Role

Read Action

Write Action

db_dtsadmin or sysadmin

Enumerate own packages.

Enumerate all packages.

View own packages.

View all packages.

Execute own packages.

Execute all packages.

Export own packages.

Export all packages.

Execute all packages in SQL Server Agent.

Import packages.

Delete own packages.

Delete all packages.

Change own package roles.

Change all package roles.

db_dtsltduser

Enumerate own packages.

Enumerate all packages.

View own packages.

Execute own packages.

Export own packages.

Import packages.

Delete own packages.

Change own package roles.

db_dtsoperator

Enumerate all packages.

View all packages.

Execute all packages.

Export all packages.

Execute all packages in SQL Server Agent.

None

Windows administrators

View execution details of all running packages.

Stop all currently running packages.

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

Exit Code

Description

0

The package executed successfully.

1

The package failed.

3

The package was canceled by the user.

4

The utility was unable to locate the requested package. The package could not be found.

5

The utility was unable to load the requested package. The package could not be loaded.

6

The utility encountered an internal error of syntactic or semantic errors in the command line.

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

Exit Code

Description

0

The utility executed successfully.

1

The utility failed.

4

The utility cannot locate the requested package.

5

The utility cannot load the requested package.

6

The utility encountered an error in the command line.

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.

SQL Server 2005 Management and Administration
By Ross Mistry, Chris Amaris, Alec Minty, and Rand Morimoto
Published by Sams Publishing
ISBN-10: 0-672-32956-5
ISBN-13: 978-0-672-32956-2
Buy this book
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved