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.
For descriptions of the different types of result sets and the variable data types that you can map to result sets, see Result Sets in the Execute SQL Task.
To map a result set to a variable
In SQL Server Data Tools (SSDT), 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 Add or Delete a Task or a Container in 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.
For descriptions of the different result sets, see Result Sets in the Execute SQL Task
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 Add, Delete, Change Scope of User-Defined Variable in a Package.
For descriptions of the variable data types that you can map to the different result sets, see Result Sets in the Execute SQL Task.
For information on how to map a variable to a single column and to map multiple variables to multiple columns, see the Populating a Variable with a Result Set section in Result Sets in the Execute SQL Task.
In the Result Name list, optionally, modify the name of the result set.
In general, you can use the column name as the result set name, or you can use the ordinal position of the column in the column list as the result set. The ability to use a column name as the result set name depends on the provider that the task is configured to use. Not all providers make column names available.
Click OK.
See Also
Tasks
Use the Values of Variables and Parameters in a Child Package