Retrieving DTS System, Package, and Log Data

Data Transformation Services (DTS) provides features for requesting information about registered components and saved packages and for retrieving the contents of log records.

Registered Components

The Application object provides access to the system, package, and log data. You create it independently of a DTS package.

Use the collections of the Application object to obtain information about several different types of registered components used by DTS. The following table describes the collections, the objects they contain, and the type of component for which information is available.

Collection	Object	Component
OLEDBProviderInfos	OLEDBProviderInfo	OLE DB providers
ScriptingLanguageInfos	ScriptingLanguageInfo	Microsoft® ActiveX® scripting languages
TaskInfos	TaskInfo	DTS task classes
TransformationInfos	TransformationInfo	DTS transformation classes

The DTS task and transformation classes include those supplied with Microsoft SQL Server™ and custom tasks and transformations implemented by other vendors and users.

Normally DTS must scan all the registered classes in the operating system registry to determine the membership of each of these collections, which can take a significant amount of time. DTS maintains a cache, also in the operating system registry, of each component type. Use the Refresh method of these collections to update the cache for that component from a full-registry scan. Set the UseCache property before iterating through the collection to make it scan the cache rather than the system registry.

Meta Data Services

DTS packages can be saved to an instance of SQL Server 2000 Meta Data Services. Lineage information is saved for such packages, if the LineageOptions property of the package specifies this be done. A package lineage record is written each time a package is executed and a step lineage record is generated for the execution of each step.

Use the GetPackageRepository method, specifying server, database and login information, to return a PackageRepository object that provides access to an instance of Meta Data Services. The following methods of the PackageRepository object return package and lineage information:

• Use the EnumPackageInfos method to return a PackageInfos collection with information about all or a subset of the packages saved in the Meta Data Services instance.
  
  
• Use the EnumPackageLineages method to return a PackageLineages collection with lineage data for a particular package version.
  
  
• Use the EnumStepLineages method to return a StepLineages collection with step lineage data for a particular package lineage (each step execution associated with a single execution of a particular package).
  
  
• Use the RemovePackageLineages method to remove some or all of the lineage data for a package version.

SQL Server Storage and Logging

All DTS packages can log to an instance of SQL Server. Log records are written to the msdb database on the server specified by the package LogServerName property each time a DTS package is executed, if the package LogToSQLServer property has been set.

How log data is written

A package log record is written by DTS for each package execution, and a step log record is written for the execution of each step.

Use the PackageLog object methods when custom tasks and the ActiveScriptTask object are to write task log records. A reference to PackageLog is passed as a parameter of the task Execute method. In task ActiveX scripts, it is available as the DTSPackageLog scripting object.

Retrieving package and log data

Use the GetPackageSQLServer method, specifying server and login information, to return a PackageSQLServer object that provides access to the package and log data on the server.

Use the PackageSQLServer object EnumPackageInfos method to return a PackageInfos collection with information about all or a subset of the packages in SQL Server storage on that server.

Use a PackageSQLServer method from the table to return the corresponding collection that contains data for all or a subset of the log records of the indicated type on the server. The removal methods will selectively remove log records of the indicated type.

Method	Returned collection	Removal method
EnumPackageLogRecords	PackageLogRecords	RemovePackageLogRecords
EnumStepLogRecords	StepLogRecords	RemoveStepLogRecords
EnumTaskLogRecords	TaskLogRecords	RemoveTaskLogRecords

In addition, RemoveAllLogRecords removes all log data for all packages from the server.

Packages Saved as Files

You can retrieve information about the contents of a DTS package storage file, which can contain multiple packages, each with multiple versions. Create a Package2 object and then use the GetSavedPackageInfos method to return a reference to a SavedPackageInfos collection with information about all the package versions contained in the file.

For more information about getting saved package information, see Retrieving DTS Information in Visual Basic.