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.

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.

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

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.