The Execute SQL task supports the following types of result sets:

• The None result set is used when the query returns no results. For example, this result set is used for queries that add, change, and delete records in a table.
  
• The Single row result set is used when the query returns only one row. For example, this result set is used for a SELECT statement that returns a count or a sum.
  
• The Full result set result set is used when the query returns multiple rows. For example, this result set is used for a SELECT statement that retrieves all the rows in a table.
  
• The XML result set is used when the query returns a result set in an XML format. For example, this result set is used for a SELECT statement that includes a FOR XML clause.
  

If the Execute SQL task uses the Full result set result set and the query returns multiple rowsets, the task returns only the first rowset. If this rowset generates an error, the task reports the error. If other rowsets generate errors, the task does not report them.

Depending on the type of connection manager, values that are returned by the SQL statement that are not already strings might be converted implicitly or explicitly to strings. An ADO or ODBC connection manager implicitly converts return values to strings. An OLE DB connection manager explicitly converts return values of the DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES data types to strings. However, an ADO.NET connection manager does not convert return values to strings.

You can bind the result set that a query returns to a user-defined variable, if the result set type is a single row, a rowset, or XML.

If the result set type is Single row, you can bind a column in the return result to a variable by using 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 name. For example, the result set name for the query SELECT Color FROM Production.Product WHERE ProductID = ? could be Color or 0. If the query returns multiple columns and you want to access the values in all columns, you must bind each column to a different variable. If you map columns to variables using numbers as result set names, the numbers reflect the order in which the columns appear in the column list of the query. For example, in the query SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?, you use 0 for the Color column and 1 for the ListPrice column. The ability to use a column name as the name of a result set depends on the provider that the task is configured to use. Not all providers make column names available.

Some queries that return a single value may not include column names. For example, the statement SELECT COUNT (*) FROM Production.Product returns no column name. You can access the return result using the ordinal position, 0, as the result name. To access the return result by column name, the query must include an AS <alias name> clause to provide a column name. The statement SELECT COUNT (*)AS CountOfProduct FROM Production.Product, provides the CountOfProduct column. You can then access the return result column using the CountOfProduct column name or the ordinal position, 0.

If the result set type is Full result set or XML, you must use 0 as the result set name.

When you map a variable to a result set with the Single row result set type, the variable must have 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 only 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 of the Object data type. The return result is a rowset object. You can write custom tasks that navigate the rowset object and access information about the columns and the data in the rowset.

The following table summarizes the data types of variables that can be mapped to result sets.

The variable can be defined in the scope of the Execute SQL task or the package. If the variable has package scope, the result set is available to other tasks and containers within the package, and is available to any packages run by the Execute Package or Execute DTS 2000 Package tasks.

When you map a variable to a Single row result set, values that the SQL statement returns that are not already strings might be converted to strings. Whether this conversion occurs, or whether this conversion is implicit or explicit, depends on the type of connection manager that is used:

• With an ADO.NET connection manager, conversion does not occur.
  
• With an ADO or ODBC connection manager, this conversion occurs implicitly.
  
• With an OLE DB or Excel connection manager, the connection manager explicitly converts values of the following types, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES, to strings.
  

For information about loading a result set into a variable, see How to: Map Result Sets to Variables in an Execute SQL Task.

For more information about the properties of result sets that you can set in SSIS Designer, click the following topic:

• Execute SQL Task Editor (Result Set Page)
  

For more information about how to set these properties in SSIS Designer, click the following topic:

• How to: Set the Properties of a Task or Container
  

• CodePlex sample, Execute SQL Parameters and Result Sets, on msftisprodsamples.codeplex.com
  

Concepts

Help and Information