Querying a DTS Package from External Sources
Data Transformation Services (DTS) package data can be made available to an external source, such as SQL Query Analyzer, by:
- Querying a package step associated with a transformation with the Transact-SQL OPENROWSET statement.
- Defining the package as a linked server and joining package data in a distributed query.
When querying package rowset data, the following conditions apply:
- The package supplying the data must be launched by the application getting the package data. You do not execute the package to send the data to the requesting application or process.
- You can only query a package step associated with a Transform Data task.
- The package must have destination columns to bind to; the destination cannot be a text file.
In DTS Designer, you make package data available by selecting the DSO rowset provider check box (on the Options tab of the Workflow Properties dialog box) for a package step associated with a Transform Data task. The data from that task then becomes available to an external data consumer.
Enable the DSO rowset provider check box only for packages that you intend to query. After the option is set, the package step where you set the option does not complete execution when the package is run normally. While the flag is set that pump task can only be accessed through OPENROWSET.
Note These methods are used typically to query packages from an external source; however, you can also query other packages from within a package by issuing OPENROWSET queries and distributed queries in an Execute SQL task or as the source for another Transform Data task.