DTS Query Strings in Visual Basic
You can assign queries to a property of the object using a query as a text string. The property name differs for each object that uses a query. The string can include carriage return/line feed pairs. When the query string is a constant and contains the " character, represent it as double quotations "", as in all Microsoft® Visual Basic® string constants.
If you are supplying source or destination queries for the DataPumpTask(2), DataDrivenQueryTask(2) or ParallelDataPumpTask, you must assign a single Select or stored procedure query to the SourceSQLStatement or DestinationSQLStatement properties. The destination query defines a rowset into which destination rows are inserted. This feature is not accessible to packages created in Data Transformation Services (DTS) Designer.
In the DynamicPropertiesTaskAssignment object of the DynamicPropertiesTask, assign a single Select or stored procedure query that returns a rowset to the SourceQuerySQL property. This query returns a single row containing a single column. Additional fields in the rowset are ignored.
Example
The following code example shows one way to assign the source and destination queries for the ParallelDataPumpTask object:
'Declare the generic and class-specific task object variables.
Dim objTask As DTS.Task
Dim objParallelPumpTask As DTS.ParallelDataPumpTask
'Create the ParallelDataPumpTask and assign the connections and queries.
Set objTask = objPackage.Tasks.New("DTSParallelDataPumpTask")
Set objParallelPumpTask = objTask.CustomTask
With objParallelPumpTask
.TransformationSetOptions = DTSTranSetOpt_DataDrivenQueries
.SourceConnectionID = 1
.SourceSQLStatement = _
"SHAPE {SELECT au_id, au_lname, au_fname FROM authors} " & _
"APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS " & _
"WHERE TA.title_id = TS.title_id} " & _
"AS title_chap RELATE au_id TO au_id)"
.DestinationConnectionID = 2
.DestinationSQLStatement = _
"SHAPE {SELECT * FROM AuthNames} " & _
"APPEND ({SELECT * FROM TitleNames} " & _
"AS TitleChap RELATE AuthID TO AuthID)"
End With
Defining Execute SQL Task Queries
With the ExecuteSQLTask object, you must assign a sequence of one or more queries to the SQLStatement property. You can insert the Transact-SQL GO statement to break the query sequence into batches.
Example
The following code example shows you how to use the SQLStatement property with a batched sequence of SQL statements:
'Declare the generic and class-specific task object variables.
Dim objTask As DTS.Task
Dim objExecSQL As DTS.ExecuteSQLTask
'Create the ParallelDataPumpTask and assign the connections and queries.
Set objTask = objPackage.Tasks.New("DTSExecuteSQLTask")
Set objExecSQL = objTask.CustomTask
With objExecSQL
.Name = "PubsEmplClearOut"
.SQLStatement = "USE DTSTest" & vbCrLf & _
"TRUNCATE TABLE EmployeeHires" & vbCrLf & "GO" & vbCrLf & _
"UPDATE MissingStuff SET MissingMI = 0" & vbCrLf & _
"UPDATE StartYearCounts SET NumberStarts = 0" & vbCrLf & "GO"
.ConnectionID = 1
End With
objPackage.Tasks.Add objTask
Defining Data Driven Queries
With the DataDrivenQueryTask(2) and the TransformationSet object of the ParallelDataPumpTask (when in DTSTranSetOpt_DataDrivenQueries mode), you can define up to four queries by assigning query strings to the InsertQuery, UpdateQuery, DeleteQuery and UserQuery properties.
Each query string consists of a sequence of one or more SQL statements or stored procedure references, although the Transact-SQL GO statement cannot be included. Use the parameter placeholder ? to indicate where you have substituted a reference to a destination column. These columns are defined by creating Column objects and adding them to one of the InsertQueryColumns, UpdateQueryColumns, DeleteQueryColumns, UserQueryColumns collections. Use the New method of the appropriate collection, followed by the Add method. Alternatively, you can create and add the Column object in a single step with the AddColumn method.
Example
The following code example shows how you could define the Insert query, the Update query, and the associated destination columns for a DataDrivenQueryTask2 object:
'Declare the generic and class-specific task object variables.
Dim objTask As DTS.Task
Dim objDDQTask As DTS.DataDrivenQueryTask
'Create the DataDrivenQueryTask and assign the connections and queries.
Set objTask = objPackage.Tasks.New("DTSDataDrivenQueryTask")
Set objDDQTask = objTask.CustomTask
With objDDQTask
.Name = "PubsDDQ"
.SourceConnectionID = 2
.SourceObjectName = "[pubs].[dbo].[employee]"
.DestinationConnectionID = 1
.DestinationObjectName = "[DTSTest].[dbo].[EmployeeHires]"
.InsertQuery = "UPDATE StartYearCounts" & vbCrLf & _
"SET NumberStarts = NumberStarts + 1 " & vbCrLf & _
"WHERE StartYear = datepart( yyyy, ? )" & vbCrLf & _
"INSERT INTO EmployeeHires (FullName, StartDate) VALUES (?, ?)"
.UpdateQuery = "UPDATE StartYearCounts" & vbCrLf & _
"SET NumberStarts = NumberStarts + 1 " & vbCrLf & _
"WHERE StartYear = datepart( yyyy, ? )" & vbCrLf & _
"UPDATE MissingStuff SET MissingMI = MissingMI + 1" & vbCrLf & _
"INSERT INTO EmployeeHires (FullName, StartDate) VALUES (?, ?)"
With .InsertQueryColumns
.AddColumn "StartDate", 1
.AddColumn "FullName", 2
.AddColumn "StartDate", 3
End With
With .UpdateQueryColumns
.AddColumn "StartDate", 1
.AddColumn "FullName", 2
.AddColumn "StartDate", 3
End With
. . .
End With
objPackage.Tasks.Add objTask
Defining Lookup Queries
For the Lookup object, a single query that generates a rowset is assigned to the Query property. This query must contain one or more ? parameter placeholders. For more information, see DTS Lookups in Visual Basic.