 |
The Execute SQL Task for SQL Server 2000 allows both input and parameters to be specified.
In this article we will be focusing on the Rowset output parameter functionality,
and what we can then do with the Recordset it returns. The Rowset parameter
takes the result set of a SELECT statement and stores it in an ADO Recordset object within a
global variable you choose. You can then retrieve the Recordset object and use
it elsewhere in your package. The example shown here queries the
INFORMATION_SCHEMA.TABLES view to return all user tables in the Northwind database.
We then implement a loop in workflow that executes a simple ActiveX
Script Task. This task
reads the current table name and appends it to a string global variable
to build a list of the tables. Finally when
there are no more records left to process the final ActiveX Script
Task displays our completed string in a message box.
The output could be more efficiently derived without the use a loop through workflow,
but this is a simple demonstration package, where normally you
would do some real work within the loop.
1. GetRSTables (Execute SQL Task)
The first part of our package execution is the workflow script for the GetTablesRS
task. To ensure the demonstration will run consistently if executed multiple
times, this script resets the MsgBoxString global variable which is used to hold our
working list of tables. The task itself is where we run the SELECT statement and assign the
result to the RSTables global variable.
2. BuildMsgBoxString (ActiveX Script Task)
This task is the start of our loop, as well as the body of the loop, but
before starting the loop we use a workflow script to check that we actually
have some records to process. If the Recordset returned by the GetTablesRS SELECT
statement is empty, we will not execute the remainder of the package.
This workflow script is shown below.
' BuildMsgBoxString - Workflow Script
Option Explicit
Function Main()
Dim oRS
Set oRS = DTSGlobalVariables("RSTables").Value
' Check we have some records in our Recordset
If oRS.BOF Then
' No records found, stop workflow
Main = DTSStepScriptResult_DontExecuteTask
Else
' We have records so continue
Main = DTSStepScriptResult_ExecuteTask
End If
Set oRS = Nothing
End Function
The task itself reads the single field value (table name) of the current
row and adds it to the string we are building in the MsgBoxString global variable.
We also call the Recordset object MoveNext method so that the
current row is advanced ready for the next iteration of our loop.
' BuildMsgBoxString - Task Script
Option Explicit
Function Main()
Dim oRS
Set oRS = DTSGlobalVariables("RSTables").Value
' Get row value and add it to the MsgBoxString global variable
DTSGlobalVariables("MsgBoxString").Value = _
DTSGlobalVariables("MsgBoxString").Value & _
oRS.Fields(0).Value & vbCrLf
' Move to the next row in preparation for loop iteration
oRS.MoveNext
Set oRS = Nothing
Main = DTSTaskExecResult_Success
End Function
3. DisplayMsgBox (ActiveX Script Task)
The final task is also the end of our loop, so again we are using a
workflow script to check if the task should be executed yet. If we have reached
the end of the Recordset then we allow the task to execute, otherwise we
prevent execution of the current task and reset the ExecutionStatus
property of the BuildMsgBoxString step to start the loop again.
' DisplayMsgBox - Workflow Script
Option Explicit
Function Main()
Dim oRS
Set oRS = DTSGlobalVariables("RSTables").Value
' Check if there are still more records in the Recordset
If oRS.EOF Then
' No more records, so continue workflow
Main = DTSStepScriptResult_ExecuteTask
Else
' More records to process, so loop back and do not execute this task
DTSGlobalVariables.Parent. _
Steps("DTSStep_DTSActiveScriptTask_BuildMsgBoxString"). _
ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSStepScriptResult_DontExecuteTask
End If
Set oRS = Nothing
End Function
When the loop has completed, we display the list of tables we
have built up through the loop as shown below.
' DisplayMsgBox - Task Script
Option Explicit
Function Main()
' Display the list of tables
MsgBox "Tables:" & vbCrLf & DTSGlobalVariables("MsgBoxString").Value
Main = DTSTaskExecResult_Success
End Function
The download to accompany this article contains a complete sample package as described above.