Guru's Guide to SQL Server Architecture and Internals, The

The DSO Rowset Provider

Another one of the cooler things you can do with a DTS package is query it with an OLE DB provider. You can flag a step of a package as a DSO rowset provider, then query the package from T-SQL using OPENROWSET and the DTSPackageDSO OLE DB provider. This allows you, for example, to expose the result of a transformation as a rowset that can be queried via T-SQL. This means, of course, that one package can serve as the data source for another package because you can obviously set up T-SQL queries as the data source in a transformation within a package. It also means that you can offload complex data processing to a DTS package, then invoke the transformation from within a T-SQL query or stored procedure. Here's an example ...