DTS Programming
Adding DTS Query Strings
Many Data Transformation Services (DTS) tasks and objects require queries to access or store database information. You assign queries to a property of the object that uses the query as a text string. You also can include carriage return/line feed pairs. Depending on the parent object, you can define query sequences and parameter placeholders. The following table defines the query types that are supported for the objects that use queries.
| Objects Using Queries | Query Type | Query String Attributes |
|---|---|---|
| DataPumpTask2, DataDrivenQueryTask2 and ParallelDataPumpTask objects | Source query | Single Select or stored procedure query that returns a rowset. Can use ? placeholder for global variable parameters, specified by InputGlobalVariableNames property. |
| DataPumpTask2, DataDrivenQueryTask2 and ParallelDataPumpTask objects | Destination query | Single Select or stored procedure query that returns a rowset. Parameters are not supported. |
| DynamicPropertiesTaskAssignment object of DynamicPropertiesTask | Property value query | Single Select or stored procedure query that returns a rowset. Rowset has single row and one column. |
| ExecuteSQLTask2 object | Executed query | Sequence of one or more SQL statements or stored procedure queries, which can contain the Transact-SQL GO statement. Can use ? placeholder for global variable parameters, specified by the InputGlobalVariableNames property. |
| DataDrivenQueryTask2 and TransformationSet objects of ParallelDataPumpTask | Action queries | Sequence of one or more SQL statements or stored procedure queries, which cannot contain the Transact-SQL GO statement. Can use ? placeholder for destination columns. |
| Lookup object | Lookup query | Single Select or stored procedure query that returns a rowset. Can use ? placeholder for parameters, specified with the Execute method of DTSLookups scripting collection. |
For more information about including query strings in DTS programs, see DTS Query Strings in Visual Basic.