OLE DB Provider for DTS Packages

The Microsoft OLE DB Provider for DTS Packages is a read-only provider that exposes Data Transformation Services Package Data Source Objects. The provider can be used to expose the rowset from a package using either OPENROWSET or referencing the package using a four-part name in a Transact-SQL statement. OPENQUERY can also be used to send a command to the provider using its command language. For more information about using the Microsoft OLE DB Provider for DTS Packages, see DTS Driver Support for Heterogeneous Data Types.

To define a linked server to access a DTS package data source object after the data source object has been defined in DTS

• Execute sp_addlinkedserver to create the linked server, specifying DTSPackageDSO as provider_name, the package name as product_name, and switches for the dtsrun command prompt utility as data_source.

  For example, to create a linked server named MyDTSPackage that accesses a DTS package saved to the file C:\Dts\DTSFilePackage.dts, execute:

  sp_addlinkedserver MyDTSPackage, 'PackageName', 'DTSPackageDSO', '/FC:\Dts\DTSFilePackage.dts'
  

  Then, the rowset exposed by this DTS package can be referenced using either the name of the DTS package or the name of a step in the DTS package:

  SELECT * FROM MyDTSPackage...DTSStep1
  

  The package name or step name are defined in DTS.

In addition, DTS packages can be referenced using the OPENROWSET function. For more information, see Querying a DTS Package from External Sources.