Issuing Distributed Queries Against Package Data
You can register a Data Transformation Services (DTS) package as a linked server and issue a distributed query against the package. This capability allows you to consolidate data from diverse sources (for example, from Oracle and DB2 data sources) in a single package, transform that data, and expose the results of the transformed distributed query to any outside data consumer.
To issue a distributed query against package data, you need to define the package as a linked server through the sp_addlinkedserver stored procedure. The following example code illustrates how to use 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 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, you can use a package step name when multiple steps in a package serve as data sources.