Using Data Transformation Services: Capture Recordset with a Global Variable

In a previous tutorial we have seen how input and output global variables can be set up, and how they can be manipulated using Activex script. The input and output parameters of a query were mapped to the input and output global variables using the Execute SQL Task, a frequently used DTS task.

In this tutorial, we will be looking at a number of rows (rowsets) returned by a select query against the SQL 2000 database and assigning them to a single global variable. This will be followed by retrieving this 'rowset' using an Activex Script via ADO. Once retrieved as a recordset, it can be displayed using VBA, or transformed to a text file using the Scripting Object. Since the Activex script requires the existence of the global variable, the first task should succeed (that is, the row sets should go into the global variable) before it can be retrieved into a recordset. Hence a workflow will be added to oversee this logistics.

The following query run against the Northwind database produces the following result as shown in the next picture. The results returned by the query (seven rows, four columns each) are shown in the results pane of the query analyzer, also seen in the same picture. This result will be assigned to a global variable in the next section.

Stuffing rowset into a global variable

Let us start off by creating a package called AdoRecords. This is saved in the SQL server with Windows authentication, shown in the next picture.

Next, an Execute SQL task is added, which requires at least one connection. This next picture shows the set up information for this connection. Since we are interested in assigning the query against the Northwind database, Northwind database has been chosen for this connection with Windows authentication.

Now from the tasks collection/ or from menu add an Execute SQL Task. If you want you may give a name to this task. As documented in detail in my other dts articles, you may build the query from scratch, or if you have already tested the query you may copy and paste it here. This is a simple enough query, as you saw earlier in this tutorial. This query returns four columns (fields) as seen in the query.

In the earlier article it was mentioned that you have the option of setting up global variables from the package properties, or it can be implemented as a part of the Execute SQL Task. For this tutorial we will be using this method. Now click on the button Parameters..., this opens up the window shown next. Since we are interested in the output only, the input parameters tab was skipped, and we are now in the output parameters tabbed window. The GUI is correctly showing the output from the query.

Now click on the button Create Global variables.... This will bring up the next screen. This screen allows you to flexibly manipulate the Global Variables. For the variable column type in Contacts. This is the variable to which the result set from the query will be assigned. For the column Type choose Other, and leave the Value column blank as shown.

Now click on OK to bring up the next screen. Click on Rowset, and in the drop-down list choose the only one in this tutorial, Contacts. In summary, through this screen you are assigning the query results to the Global variable, Contacts. Click OK to this screen and the assignment is completed.

Retrieve rowset using Activex Script

In this part, we will take a look at the Global variable Contacts created in the previous step using Activex Script. Pick up an Activex task and instantiate it on the design pane. As you click the Activex task in the tasks collection, an Activex icon is placed on the design pane, simultaneously opening the window to write your Activex Script as shown in the next screen. Please review my previous tutorials on the details about this window and its several controls.

In this screen we declare an object RS and assign to it an ADODB.Recordset object created using the CreateObject() method. In the next line,  we associate this record set with a DTSGlobalVariable. Now it is easy to cycle through the recordset to look at its properties, such as the number of records, the various fields and their properties, and so on. This is what is being achieved in the For ...Next statement. The script used to retrieve the recordset and display in a MsgBox is shown here.

'**************************************************************
'  Visual Basic ActiveX Script
'**************************************************************
Function Main()
dim cnt
dim RS
set RS = CreateObject("ADODB.Recordset")
set RS = DTSGlobalVariables("Contacts").value
for cnt = 1 to RS.RecordCount
MsgBox ( "Contact Name: " & RS.Fields("ContactName").value _
&" ; ContactTitle:  " & RS.Fields("ContactTitle").value _
&" ; City : " & RS.Fields("City").value _
&" ; Country:   " & RS.Fields("Country" ) & vbcrlf  )
RS.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function

The package now contains an ExecuteSQLTask and an Activex Task. We want the Activex task to be processed after the ExecuteSQL task. We invoke the services of a Work Flow. Highlight both of the tasks and set up the work flow as shown here(review the previous tutorials for a more detailed explanation). We want the Source Step to succeed (complete is also acceptable) before the Destination step to be processed.

Now, we will test this package. Save the package and from menu item Package's drop-down click on Execute. This runs the package. The result is shown in the next picture. The MsgBox will pop up seven times, each time bringing a row from the queries result set and finally a DTS message (Package Execution Results window) about the success of the run. The picture shows the first row in the message box.

Summary

Assigning records returned from SQL Queries to a global variable is described in detail. The record set was displayed using a message box, but using the powerful scripting support available in VBA, it is possible to take these into a variety of forms, text files, Excel spread sheets, and so forth.