How to retrieve rowset data stored in a global variable (Enterprise Manager)

How to retrieve rowset data stored in a global variable (Enterprise Manager)

To retrieve the rowset data stored in a global variable using the Execute SQL task

1. From the Task toolbar, drag a Microsoft® ActiveX® Script task onto the Data Transformation Services (DTS) design sheet.
   
   
2. In the ActiveX Script Properties dialog box, after the Function Main() statement, type the following Microsoft Visual Basic® Scripting Edition (VBScript) code:

   dim countr
   dim RS
   set RS = CreateObject("ADODB.Recordset")
   set RS = DTSGlobalVariables("Authors").value
   
   for countr = 1 to RS.RecordCount
       MsgBox "The author ID is " & RS.Fields("au_id").value
       RS.MoveNext
   Next
   
   Main = DTSTaskExecResult_Success
   

3. On the design sheet, click the Execute SQL task, and then CTRL-click the ActiveX Script task.
   
   
4. On the Workflow menu, click On Success or On Completion.

   The au_id column for each row returned from the SELECT statement is displayed.