Using Data Transformation Services: Capture Recordset with a Global Variable - Stuffing rowset into a global variable
(Page 3 of 4 )
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.
Next: Retrieve rowset using Activex Script >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
