Dela via

  • Artikel

Working with Result Sets in the Execute SQL Task

In an Integration Services package, whether a result set is returned to the Execute SQL task depends on the type of SQL command that the task uses. For example, a SELECT statement typically returns a result set, but an INSERT statement does not.

What the result set contains also varies by SQL command. For example, the result set from a SELECT statement can contain zero rows, one row, or many rows. However, the result set from a SELECT statement that returns a count or a sum contains only a single row.

Working with result sets in an Execute SQL task is more than just knowing whether the SQL command returns a result set and what that result set contains. There are additional usage requirements and guidelines to successfully use result sets in the Execute SQL task. The remainder of this topic covers these usage requirements and guidelines:

  • Specifying a Result Set Type

  • Populating a variable with a result set

  • Configuring results sets in the Execute SQL Task Editor

Specifying a Result Set Type

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.

Populating a Variable with a Result Set

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.

Result set type

Data type of variable

Type of object

Single row

Any type that is compatible with the type column in the result set.

Not applicable

Full result set

Object

If the task uses a native connection manager, including the ADO, OLE DB, Excel, and ODBC connection managers, the returned object is an ADO Recordset.

If the task uses a managed connection manager, such as the ADO.NET connection manager, then the returned object is a System.Data.DataSet.

XML

String

String

XML

Object

If the task uses a native connection manager, including the ADO, OLE DB, Excel, and ODBC connection managers, the returned object is an MSXML6.IXMLDOMDocument.

If the task uses a managed connection manager, such as the ADO.NET connection manager, the returned object is a System.Xml.XmlDocument.

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.

Configuring Result Sets in the Execute SQL Task

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

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

External Resources

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.