Accessing Package Data from External Data Sources

DTS Designer package data can be made available to an external source, such as SQL Server Query Analyzer, by:

• Querying the step with the Transact-SQL OPENROWSET statement.
• Defining the package as a linked server and joining package data in a distributed query.

You make package data available by enabling the DSO rowset provider option (on the General tab of the Workflow Properties dialog box) for a package step. The data at that point in the package workflow then becomes available to an external data consumer.

You should only enable the DSO rowset provider option for packages that you intend to query; when the option is set, the package step where you set the option does not complete execution. If you want to set up a package that both executes and serves as a data source, you may need to set up a separate branch in the package workflow to handle external queries.

Note These methods are typically used 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.

Querying a Package with OPENROWSET

You can run queries against a DTS package by using a Transact-SQL OPENROWSET statement.

To ready a package to serve as a data source, enable the DSO rowset provider option (on the General tab of the Workflow Properties dialog box) for a package step.

The following considerations apply to a package that will be queried with OPENROWSET.

• Data Transformation Services 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 information about 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 set 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 General 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 on using the Transact-SQL OPENROWSET statement, see OPENROWSET.

Querying a Package Saved to a File

Suppose you want to use SQL Server Query Analyzer to query a package saved to the Data Transformation Services file Dso.dts on your local server. The query statement would look something like:

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

In the OPENROWSET statement:

• The provider_name argument is always DTSPackageDSO, a Microsoft® Windows® registry entry that functions as the package OLE DB provider.
• The provider_string argument contains the Data Transformation Services 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 had enabled more than one DSO rowset provider option setting in the above package, the following statement uses OPENROWSET 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

Suppose you want to use SQL Server Query Analyzer to query the Microsoft SQL Server™ package Sqlpackage on your local server. The query statement would look something like:

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

In the OPENROWSET statement:

• The provider_name argument is always DTSPackageDSO, as explained earlier.
• 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 had saved multiple versions of this package and wanted to reference a specific version, you would need to include the version 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). Querying a version of the package described above would look something like:

SELECT * FROM OPENROWSET('DTSPackageDSO',

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

Issuing Distributed Queries Against Package Data

You can also register a package as a linked server and issue a distributed query against the package. To do so, you need to define the package as a linked server through the sp_addlinkedserver stored procedure. This is an example of using sp_addlinkedserver against a DTS package:

sp_addlinkedserver 'DTSOLEDBPkg', 'PackageName', 'DTSPackageDSO', '/FC:\Dts\Dts01.dts')

In the sp_addlinkedserver command:

• DTSOLEDBPkg is the name of the linked server you want to create.
• PackageName is the product name of the OLE DB data source; in this context, you can provide any name or a null string.
• DTSPackageDSO is the name of the DTS package OLE DB provider.
• The last argument specifies the location of the Data Transformation Services file, Dts01.dts.

After you have defined the package as a linked server, you can execute distributed queries that include the package as a data source. Following is an example of a distributed query. It performs a join operation on the Orders table in the Northwind database with a package that gets data from a Customer table on an Oracle server. The query assumes the Orders and Customers tables have a common key, which is CustomerID.

SELECT a.OrderID, a.CustomerID, a.OrderDate, b.Companyname, b.Region
FROM Orders AS a, dtsLink...packageNameOracle AS b
WHERE a.CustomerID = b.CustomerID

In the above query, packageNameOracle is the DTS package name; however, a package step name can be used instead. You would want to use a package step name when multiple steps in a package serve as data sources.