A DTS package may function as the source of a rowset sent to an external consumer. This is done by the external consumer creating an instance of a DataSource object with the provider DTSPackageDSO (PDSO). This provider is an in-process wrapper that launches the package, receives one or more rowsets from it, and returns these rowsets to the external consumer.
A rowset is returned from a Step object by setting its IsPackageDSORowset property to True. If the package is not launched using the PDSO, then any Step object marked with the IsPackageDSORowset property is skipped, because there is no external client to process the results of the rowset.
One of the most powerful features of DTS Packages as DataSource objects is step-chaining. Using step-chaining, you can use the data destination of one step as a data source for a subsequent step. For example, the data destination of step 1 could be the data source for step 2, and the data destination of step 2 could be the data destination of step 3.
The external consumer specifies which package to load by setting either the DBPROP_INIT_DATASOURCE or DBPROP_INIT_PROVIDERSTRING properties to a string in the same format as the command prompt for the dtsrun utility (for example, /ffilename). Internally, the dtsrun utility is not launched, but the PDSO creates an instance of a utility object shared with dtsrun, which parses the command prompt and loads the requested package.
The PDSO operates between the executing package and the external consumer, receiving rowsets from the package, returning its own IRowset interface as a proxy rowset to the external consumer, and delegating all calls on its IRowset interface to the rowset it receives from the package. Each Step object behaves like a virtual table, allowing limitation of results to a specific Step object, or returning them all.
For any rowset returned, the Step object does not complete execution until the external consumer completes processing the rowset by finally releasing the PDSO’s proxy rowset and all associated interfaces and handles (for example, IAccessor, HACCESSOR, or HROW).
The simplest way to obtain a package rowset is to obtain a session from the data source using the IDBCreateSession interface, requesting the IOpenRowset interface, and then calling the OpenRowset method using the returned pointer. The table name passed to the OpenRowset method is interpreted as a Step object name.
If the table name is not empty, only results from this named Step object will be returned. If the Step object is not found, is skipped for other package-defined reasons, or does not return a result set, then DB_E_NOTABLE is returned. The Package object continues executing after the specified Step object completes its execution and its rowset is released.
If the table name is blank, the first result set encountered is returned, and subsequent steps returning result sets are skipped. If a Step object returns no results, then DB_E_NOTABLE is returned.
Returning multiple result sets is accomplished by executing a query using the ICommand interface, which is the only way the IMultipleResults interface can be returned. Each call to the IMultipleResults interface retrieves the next matching rowset. The PDSO supports this simple SQL syntax:
SELECT *
SELECT * FROM ALL
SELECT * FROM PackageName
SELECT * FROM <StepName>
The first three forms are equivalent and cause all result sets to be returned using the IMultipleResults interface. The final form limits the returned result set to the named Step object.
If the ICommand::Execute interface is requested to return an IRowset interface instead of an IMultipleResults interface, then the logic is the same as for the IOpenRowset interface because no further results can be requested. If only the results from a singe Step object are requested, then the behavior is identical to that of the IOpenRowset interface, although the single result set is returned using the ICommand interface. If all possible results are requested, then they are returned using the IMultipleResults::GetResult method.
Note When executing a package using OPENROWSET, the Step object name must be specified, and only one rowset can be returned.
A Step object that is intended to return a rowset using the PDSO must both use a DataPumpTask object and have its IsPackageDSORowset property set to True. If the IsPackageDSORowset property is True and the task is not a DataPumpTask object, then an error occurs.
For more complex operations, such as selectively specifying skipping, a Boolean global variable (IsPackageDSO), is added to the GlobalVariables collection, and its value set to True if the package was launched by a PDSO. This allows the ActiveXScriptTask object to perform specific testing.
A DataPumpTask object may be configured to return a rowset in these ways:
For the DataPumpTask and DataDrivenQueryTask objects, the SourceObjectName and SourceSQLStatement properties correspond to the IOpenRowset and ICommand interfaces, respectively. This determines whether the Step object returns one or many rowsets. If a DataPumpTask Step object’s IsPackageDSORowset property is set to True, then the IMultipleResults interface is requested. Otherwise, the Package object requests only a single result set, regardless of whether the SourceObjectName or SourceSQLStatement properties is used.
If no results are returned from the source PDSO in a DataPumpTask object, and the Step object was executing in IsPackageDSORowset mode, the effect is as if the Step object were skipped and no error is returned. Otherwise, an error is returned, as is the case when any DataPumpTask object cannot acquire a rowset.
Because the package is inherently multithreaded, and because the DTSRowQueue requires two consumers (one to insert and one to call the GetNextRows method), the PDSO must launch the package on a worker thread so that it can return control to the PDSO consumer. Therefore, the package functions as a background writer task that makes data available in the form of a rowset, and the consumer functions as a foreground reader task that processes the data.