Using Data Transformation Services: Capture Recordset with a Global Variable
(Page 1 of 4 )
Have you ever wanted to learn how a number of rows returned by a select query against the SQL 2000 database can be assigned a single global variable? This article discusses that topic, as well as retrieving the rowset using an Activex Script via ADO, and related information.
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.
Next: The Query on the Northwind database >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
