Querying a Package with OPENROWSET

You can run queries against a Data Transformation Services (DTS) package by using a Transact-SQL OPENROWSET statement.

To prepare a package to serve as a data source, select the DSO rowset provider check box (on the Options tab of the Workflow Properties dialog box) for a package step.

Before querying a package with OPENROWSET, consider the following:

• DTS uses its own OLE DB provider, DTSPackageDSO. When you use OPENROWSET, you only specify its provider_name, provider_string, and query arguments:
  • Use 'DTSPackageDSO' for the provider_name argument.
    
    
  • Use any combination of dtsrun command switches necessary to describe the package for the provider_string argument. For more information about the dtsrun command switches, see dtsrun Utility.
    
    
  • Use either 'SELECT *', 'SELECT * FROM <package name>', or 'SELECT * FROM <package step name>' for the query argument.
• If you select the DSO rowset provider option for more than one step in a package, you need to specify the package step name in the OPENROWSET query argument. The package step name can be copied from the Options tab of the Workflow Properties dialog box for the step whose data you want to query.
  
  
• If you saved multiple versions of the package, you can specify the package version using the dtsrun command switch /vpackage_version_guid_string. If you do not specify a package version, the last-saved version is used.

For more information about using the Transact-SQL OPENROWSET statement, see OPENROWSET.

Querying a Package Saved to a File

Suppose you want to use SQL Query Analyzer to query a package saved to the package Dso.dts on your local server. Use the following query statement:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/FC:\Dts\Dso.dts', 'Select *')

In the OPENROWSET statement:

• The provider_name argument is always DTSPackageDSO, an entry in the registry that functions as the package OLE DB provider.
  
  
• The provider_string argument contains the structured storage file name, preceded by the /F dtsrun command switch.
  
  
• The query argument is a SELECT * statement used to pass through the rowset data.

If you selected the DSO rowset provider check box for more than one Transform Data task in the above package, you can use the following code to query the second package step in Dso.dts:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/FC:\Dts\Dso.dts',
                  'SELECT * FROM DTSStep_DTSDataPumpTask_2')

Querying a Package Saved to SQL Server

In the following example, the package, Sqlpackage, is saved to an instance of Microsoft® SQL Server™ running on your local server. To query the package using SQL Query Analyzer, use the following query statement:

SELECT * FROM OPENROWSET('DTSPackageDSO', '/Usa /P /S /NSqlpackage', 'Select *')

In the OPENROWSET statement:

• The provider_name argument is always DTSPackageDSO.
  
  
• The provider_string argument contains the following dtsrun command switches: /U for the user ID, /P for the password (blank here), /S for the network name of the server (if the server is local, server_name can be omitted, as shown here), /N for the name of the SQL Server package.
  
  
• The query argument is a SELECT * statement used to pass through the rowset data.

If you save multiple versions of this package and want to reference a specific version, you need to include the version globally unique identifier (GUID) string after the /V dtsrun command switch. The version GUID can be obtained from the General tab of the DTS Package Properties dialog box. Use the following code to querying a version of the package described above:

SELECT * FROM OPENROWSET('DTSPackageDSO',
      '/Usa /P /S /Nsqlpackage /V{3C904BA2-4E83-11D2-BB38-00C04FA35397}',
      'Select *')