Special Offers

MCSE Training Kit (Exam 70-228): Microsoft® SQL Server™ 2000 System Administration
Author Microsoft Corporation
Pages 720
Disk 1 Companion CD(s)
Level All Levels
Published 03/28/2001
ISBN 9780735612471
ISBN-10 0-7356-1247-1
Price(USD) $59.99
To see this book's discounted price, select a reseller below.

More Information

About the Book
Table of Contents
Sample Chapter
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books


Chapter 7: Populating a Database continued

Lesson 4: Working with DTS Packages

Now that you understand how to create and execute a DTS package, you will learn about storing and securing DTS packages. In addition, you will learn additional methods for executing a saved DTS package, as well as using DTS package logs to troubleshoot problems that occur during the execution of a DTS package. You will also learn about editing DTS packages when data sources and destinations are unavailable. Finally, you will learn about browsing and sharing meta data about DTS packages.

After this lesson, you will be able to

  • Understand the different storage options for DTS packages
  • Secure a DTS package
  • Execute DTS packages from the command prompt
  • Edit a DTS package when a data source or destination is unavailable

Estimated lesson time: 15 minutes

Understanding DTS Package Storage Options

As discussed earlier, you can store a DTS package using SQL Server 2000, SQL Server Meta Data Services, a structured storage file, or a Visual Basic file. You use each storage format to accomplish different objectives, based on their capabilities.

SQL Server 2000

Saving a DTS package to SQL Server 2000 allows you to save a DTS package as a binary object in the sysdtspackages table in the msdb database on any SQL Server 2000 instance in your organization. You can keep an inventory of DTS packages in one location on your network. Each version of a DTS package is saved, preserving the development history of a package. This allows you to retrieve and edit any version of a DTS package you choose, not just the most recent version.

When you save a DTS package to SQL Server 2000, you can set both an owner password and a user password. These passwords are used in addition to the Windows Authentication or SQL Server Authentication used to connect to SQL Server 2000. Users with access to the user password can run a DTS package, but cannot open or edit a package. Users must have access to the owner password to open or edit a DTS package. This extra level of security is strongly recommended, particularly on a SQL Server 2000 instance that permits SQL Server Authentication.

Meta Data Services

Saving a DTS package to SQL Server Meta Data Services allows you to track package version, meta data, and data lineage (original data source and transformations) information. You can view version history for packages saved to Meta Data Services in SQL Server Enterprise Manager and can open the version you want. DTS uses the DTS Information Model to store meta data transformation information in Meta Data Services. The DTS Information Model describes the data transformations, how they are grouped, and the types of data accessed. This meta data information can be stored, browsed, and reused.

Saving a DTS package in Meta Data Services allows you to record and track two types of data lineage, row-level and column-level. Row-level data lineage records the source of the data in each row and the transformations that were applied to it. This is useful for providing an audit trail of package execution and row-level transformations. You must specifically enable row-level data lineage by creating a column in each row to contain a lineage identifier. Column-level data lineage provides information regarding the package version and the database tables and columns used. You can browse packages and versions to determine whether any package saved in Meta Data Services uses a particular table or column. This can be particularly useful if a data source is later determined to be of questionable value (such as corrupt or inaccurate data). You enable column-level data lineage by scanning and saving the meta data once a DTS package has been saved to Meta Data Services.

Meta Data Services does not support package-level security.

Structured Storage File

Saving a DTS package to a structured storage file allows you to copy, move, and send a package across the network (such as in a mail message) without storing the package in a database or a repository. Multiple versions and multiple packages can be stored within a single file. Saving to a structured storage file also supports owner and user passwords. You can use the command-prompt DTS package execution utilities to execute a DTS package saved as a structured storage file. A DTS package saved as a structured storage file has a .DTS extension.

Visual Basic File

Saving a DTS package to a Visual Basic file allows the DTS package to be edited using Visual Basic or Visual C++. This allows developers to incorporate DTS packages into Visual Basic programs or used as prototypes to reference the components of the DTS object model. A DTS package saved as a Visual Basic file cannot be reopened and edited with DTS Designer.

Using DTS Package Execution Utilities

DTS provides two command-prompt package execution utilities: the DTS Run utility and the Dtsrun command. Using either of these utilities, a user can execute a DTS package without opening it. If the DTS package was saved using an owner or a user password, you must provide the appropriate password. If the DTS package was saved to a structured file, you must specify the filename. If the DTS package was saved to SQL Server, you must specify connection information to the SQL Server instance containing the DTS package.

DTS Run Utility

The DTS Run Utility is an interactive utility that allows you to connect to a server or specify a file, specify scheduling options, identify and enable an event log, add new global variables and change the properties of existing variables, and create a Dtsrun command with either clear or encrypted arguments for later use. To access the DTS Run utility, execute Dtsrunui from a command prompt.


The Dtsrun command allows you to run a DTS package from a command prompt using arguments and embed this command in a batch file. For example, to execute the DTS package named MyPackage saved to C:\DTSPackages\DTS1.dts with an owner password of Password, you would type the following command:

dtsrun /FC:\DTSPackages\DTS1.dts /NMyPackage /MPassword

A DTS package normally executes using the security context of the user executing it. However, if a DTS package is scheduled for execution, it is run in the security context of the owner of the SQL Server Agent job that runs the DTS package. Jobs are covered in Chapter 13.

Using DTS Package Logs and Exception Files

DTS records information about the success or failure of each step in the execution of a package in the DTS package log. This includes start and end times for each step and the length of execution. If a step was not run, this is also recorded. Package logging occurs only when SQL Server 2000 is running on a Windows 2000 server. DTS also uses DTS exception files to save error information about rows of data that were not copied and to save the actual source and destination rows that failed.

Performing Disconnected Edits

You can use DTS Designer to open and edit an existing DTS package. Normally, connectivity to each data source and destination is required to protect against setting invalid properties. However, sometimes those connections are not available. DTS Designer contains a Disconnected Edit feature that allows you to modify a DTS package when you cannot connect to the original data sources and destinations (such as a DTS package created on a test system for use on a production system in a different site). You can also use this feature to view and modify properties that are not exposed through DTS Designer (such as task names, step names, and connection names).

Disconnected Edit edits properties directly and so should only be used by advanced users when there are no alternate methods of modifying values.

Lesson Summary

DTS packages can be stored in several different ways. Storing a DTS package either in SQL Server 2000 or in a structured storage file allows you to set a user password as well as an owner password. This allows a user to run a DTS package from a command prompt using one of the DTS package execution utilities without being able to view or edit the package. However, storing a DTS package using SQL Server Meta Data Services allows extensive row-level and column-level tracking of data lineage and transformation meta data information. This is particularly useful when data is being imported and transformed from a wide variety of sources. Finally, DTS Designer provides the Disconnected Edit feature, which allows you to edit a DTS package when the underlying data connections are unavailable.

Previous   |  Table of Contents   |   Next

Top of Page

Last Updated: Saturday, July 7, 2001