How to: Map Result Sets to Variables in an Execute SQL Task
This topic describes how to create a mapping between a result set and a variable in an Execute SQL task. Mapping a result set to a variable makes the result set available to other elements in the package. For example, a script in a Script task can read the variable and then use the values from the result set or an XML source can consume the result set stored in a variable. If the result set is generated by a parent package, the result set can be made available to a child package called by an Execute Package task by mapping the result set to a variable in the parent package, and then creating a parent package variable configuration in the child package to store the parent variable value.
To map a result set to a variable
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
Click the Control Flow tab.
If the package does not already include an Execute SQL task, add one to the control flow of the package. For more information, see How to: Add a Task or a Container to a Control Flow.
Double-click the Execute SQL task.
In the Execute SQL Task Editor dialog box, on the General page, select the Single row, Full result set, or XML result set type.
Click Result Set.
To add a result set mapping, click Add.
From the Variables Name list, select a variable or create a new variable. For more information, see How to: Add a Variable to a Package Using the Variables Window.
Important
If the result set is Single row, the result set must map to a variable that has a data type that is compatible with the data type of the column that the result set contains. For example, a result set that contains a column with a string data type cannot map to a variable with a numeric data type. An XML result set can map to a variable with the String or Object data type. If the variable has the String data type, the Execute SQL task returns a string and the XML source can consume the XML data. If the variable has the Object data type, the Execute SQL task returns a Document Object Model (DOM) object. A Full result set must map to a variable with the Object data type.
In the Result Name list, optionally, modify the name of the result set.
Important
The result set can have mappings to several different variables.
Click OK.
See Also
Tasks
Creating Package Configurations
How to: Use Values of Parent Variables in Child Packages
Concepts
Execute SQL Task
Execute Package Task
Package Configurations
Integration Services Variables