Chapter 7: Populating a Database continued
Estimated lesson time: 15 minutes
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.
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.
Disconnected Edit edits properties directly and so should only be used by advanced users when there are no alternate methods of modifying values.
Last Updated: Saturday, July 7, 2001