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