INF: SQL Server Data Transformation Services and the Microsoft Repository

ID: Q246333

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

The purpose of this article is to describe the interaction of SQL Server's Data Transformation Services (DTS) with the Microsoft Repository and the functionality provided by packages saved into the Microsoft Repository.

MORE INFORMATION

Microsoft Data Transformation Services uses the Microsoft repository for three basic functions. For performance reasons, it is recommended that you use Local SQL Server or file for package storage, unless you have a need for one or more of the features provided by repository packages.

Package Metadata

The first feature supported by Repository packages is the ability to save metadata relating to a package's data providers. Column metadata can be saved for those providers that support the IDBSchemaRowset interface. Text files and Excel files do not support this interface.

The metadata view in the SQL Server Enterprise Manager can be used to see which tables and columns are used as either a source or destination in a repository package. This can ease the task of tracking database dependencies for packages. In order to use this feature, you must enable scanning options from the save to repository dialog.

Saves and loads of packages to and from the repository are slower than to server or file. Saving with column metadata adds yet another layer, since metadata for every table and column in any database referenced by the package will be scanned in, whether the package uses it or not.

Package Lineage

The second feature provided by packages stored in the Microsoft Repository is package lineage. With package lineage, each execution of each version of a package generates a unique ID, called a GUID, and a short ID. Either of these IDs can be written as a column in the destination table. This acts as a row level audit trail that can be used to determine exactly which package and what version of the package was used to create a particular row in the destination table.

To enable storage of row level lineage you must check the lineage options on the Advanced tab of the Package properties dialog box. You should also map either the short or long lineage ID source column to a column in the destination table.

When the package is executed, the lineage column is populated. You can then copy a lineage value from a row in the destination table into the metadata lineage view in Enterprise Manager to get information about the package that created the row.

Upgrade Path

Finally, use of the Microsoft Repository provides an upgrade path to move metadata and packages to products of other data warehousing alliance members such as Sagent and Platinum.

Additional query words: Repository, DTS, Data Transformation Services

Keywords : SSrvTrans kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo