Share via

  • Article

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.

For more information, see Working with Result Sets in the Execute SQL Task.

To map a result set to a variable

  1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.

  2. In Solution Explorer, double-click the package to open it.

  3. Click the Control Flow tab.

  4. 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 or Delete a Task or a Container in a Control Flow.

  5. Double-click the Execute SQL task.

  6. In the Execute SQL Task Editor dialog box, on the General page, select the Single row, Full result set, or XML result set type.

  7. Click Result Set.

  8. To add a result set mapping, click Add.

  9. From the Variables Name list, select a variable or create a new variable. For more information, see How to: Add or Delete a Variable in a Package by 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.

  10. In the Result Name list, optionally, modify the name of the result set.

    Important

    The result set can have mappings to several different variables.

  11. Click OK.