Επεξεργασία

Κοινή χρήση μέσω

Execute SQL Task

  • Άρθρο

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Execute SQL task for the following purposes:

  • Truncate a table or view in preparation for inserting data.

  • Create, alter, and drop database objects such as tables and views.

  • Re-create fact and dimension tables before loading data into them.

  • Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.

  • Save the rowset returned from a query into a variable.

The Execute SQL task can be used in combination with the Foreach Loop and For Loop containers to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Execute SQL task repeatedly. For example, using the Foreach Loop container, a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.

Connect to a data source

The Execute SQL task can use different types of connection managers to connect to the data source where it runs the SQL statement or stored procedure. The task can use the connection types listed in the following table.

Connection type Connection manager
EXCEL Excel Connection Manager
OLE DB OLE DB Connection Manager
ODBC ODBC Connection Manager
ADO ADO Connection Manager
ADO.NET ADO.NET Connection Manager
SQLMOBILE SQL Server Compact Edition Connection Manager

Create SQL statements

The source of the SQL statements used by this task can be a task property that contains a statement, a connection to a file that contains one or multiple statements, or the name of a variable that contains a statement. The SQL statements must be written in the dialect of the source database management system (DBMS). For more information, see Integration Services (SSIS) Queries.

If the SQL statements are stored in a file, the task uses a File connection manager to connect to the file. For more information, see File Connection Manager.

In SSIS Designer, you can use the Execute SQL Task Editor dialog box to type SQL statements, or use Query Builder, a graphical user interface for creating SQL queries.

Note

Valid SQL statements written outside the Execute SQL task may not be parsed successfully by the Execute SQL task.

Note

The Execute SQL Task uses the RecognizeAll ParseMode enumeration value. For more information, see ManagedBatchParser Namespace.

Send multiple statements in a batch

If you include multiple statements in an Execute SQL task, you can group them and run them as a batch. To signal the end of a batch, use the GO command. All the SQL statements between two GO commands are sent in a batch to the OLE DB provider to be run. The SQL command can include multiple batches separated by GO commands.

There are restrictions on the kinds of SQL statements that you can group in a batch. For more information, see Batches of Statements.

If the Execute SQL task runs a batch of SQL statements, the following rules apply to the batch:

  • Only one statement can return a result set and it must be the first statement in the batch.

  • If the result set uses result bindings, the queries must return the same number of columns. If the queries return a different number of columns, the task fails. However, even if the task fails, the queries that it runs, such as DELETE or INSERT queries, may succeed.

  • If the result bindings use column names, the query must return columns that have the same names as the result set names that are used in the task. If the columns are missing, the task fails.

  • If the task uses parameter binding, all the queries in the batch must have the same number and types of parameters.

Run parameterized SQL commands

SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.

Note

You can use parameters in an Execute SQL task only if the data provider supports them.

Specify a result set type

Depending on the type of SQL command, a result set may or may not be returned to the Execute SQL task. For example, a SELECT statement typically returns a result set, but an INSERT statement does not. The result set from a SELECT statement can contain zero rows, one row, or many rows. Stored procedures can also return an integer value, called a return code, that indicates the execution status of the procedure. In that case, the result set consists of a single row.

Configure the Execute SQL task

You can configure the Execute SQL task in the following ways:

  • Specify the type of connection manager to use to connect to a database.

  • Specify the type of result set that the SQL statement returns.

  • Specify a time-out for the SQL statements.

  • Specify the source of the SQL statement.

  • Indicate whether the task skips the prepare phase for the SQL statement.

  • If you use the ADO connection type, you must indicate whether the SQL statement is a stored procedure. For other connection types, this property is read-only and its value is always false.

You can set properties programmatically or through SSIS Designer.

General Page - Execute SQL Task Editor

Use the General page of the Execute SQL Task Editor dialog box to configure the Execute SQL task and provide the SQL statement that the task runs.

To learn more about the Transact-SQL query language, see Transact-SQL Reference (Database Engine).

Static Options

Name
Provide a unique name for the Execute SQL task in the workflow. The name that is provided will be displayed within SSIS Designer.

Description
Describe the Execute SQL task. As a best practice, to make packages self-documenting and easier to maintain, describe the task in terms of its purpose.

TimeOut
Specify the maximum number of seconds the task will run before timing out. A value of 0 indicates an infinite time. The default is 0.

Note

Stored procedures do not time out if they emulate sleep functionality by providing time for connections to be made and transactions to complete that is greater than the number of seconds specified by TimeOut. However, stored procedures that execute queries are always subject to the time restriction specified by TimeOut.

CodePage
Specify the code page to use when translating Unicode values in variables. The default value is the code page of the local computer.

Note

When the Execute SQL task uses an ADO or ODBC connection manager, the CodePage property is not available. If your solution requires the use of a code page, use an OLE DB or an ADO.NET connection manager with the Execute SQL task.

TypeConversionMode
When you set this property to Allowed, the Execute SQL Task will attempt to convert output parameter and query results to the data type of the variable the results are assigned to. This applies to the Single row result set type.

ResultSet
Specify the result type expected by the SQL statement being run. Choose among Single row, Full result set, XML, or None.

ConnectionType
Choose the type of connection manager to use to connect to the data source. Available connection types include OLE DB, ODBC, ADO, ADO.NET and SQLMOBILE.

Related Topics: OLE DB Connection Manager, ODBC Connection Manager, ADO Connection Manager, ADO.NET Connection Manager, SQL Server Compact Edition Connection Manager

Connection
Choose the connection from a list of defined connection managers. To create a new connection, select <New connection...>.

SQLSourceType
Select the source type of the SQL statement that the task runs.

Depending on the connection manager type that Execute SQL task uses, you must use specific parameter markers in parameterized SQL statements.

This property has the options listed in the following table.

Value Description
Direct input Set the source to a Transact-SQL statement. Selecting this value displays the dynamic option, SQLStatement.
File connection Select a file that contains a Transact-SQL statement. Setting this option displays the dynamic option, FileConnection.
Variable Set the source to a variable that defines the Transact-SQL statement. Selecting this value displays the dynamic option, SourceVariable.

QueryIsStoredProcedure
Indicates whether the specified SQL statement to be run is a stored procedure. This property is read/write only if the task uses the ADO connection manager. Otherwise the property is read-only and its value is false.

BypassPrepare
Indicate whether the SQL statement is prepared. true skips preparation; false prepares the SQL statement before running it. This option is available only with OLE DB connections that support preparation.

Related Topics: Prepared Execution

Browse
Locate a file that contains a SQL statement by using the Open dialog box. Select a file to copy the contents of the file as a SQL statement into the SQLStatement property.

Build Query
Create a SQL statement using the Query Builder dialog box, a graphical tool used to create queries. This option is available when the SQLSourceType option is set to Direct input.

Parse Query
Validate the syntax of the SQL statement.

SQLSourceType Dynamic Options

SQLSourceType = Direct input

SQLStatement
Type the SQL statement to execute in the option box, or click the browse button (...) to type the SQL statement in the Enter SQL Query dialog box, or click Build Query to compose the statement using the Query Builder dialog box.

Related Topics: Query Builder

SQLSourceType = File connection

FileConnection
Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related Topics: File Connection Manager, File Connection Manager Editor

SQLSourceType = Variable

SourceVariable
Select an existing variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services (SSIS) Variables, Add Variable

Parameter Mapping Page - Execute SQL Task Editor

Use the Parameter Mapping page of the Execute SQL Task Editor dialog box to map variables to parameters in the SQL statement.

Options

Variable Name
After you have added a parameter mapping by clicking Add, select a system or user-defined variable from the list or click <New variable...> to add a new variable by using the Add Variable dialog box.

Related Topics: Integration Services (SSIS) Variables

Direction
Select the direction of the parameter. Map each variable to an input parameter, output parameter, or a return code.

Data Type
Select the data type of the parameter. The list of available data types is specific to the provider selected in the connection manager used by the task.

Parameter Name
Provide a parameter name.

Depending on the connection manager type that the task uses, you must use numbers or parameter names. Some connection manager types require that the first character of the parameter name is the @ sign , specific names like @Param1, or column names as parameter names.

Parameter Size
Provide the size of parameters that have variable length, such as strings and binary fields.

This setting ensures that the provider allocates sufficient space for variable-length parameter values.

Add
Click to add a parameter mapping.

Remove
Select a parameter mapping in the list and then click Remove.

Result Set Page - Execute SQL Task Editor

Use the Result Set page of the Execute SQL Task Editor dialog to map the result of the SQL statement to new or existing variables. The options in this dialog box are disabled if ResultSet on the General page is set to None.

Options

Result Name
After you have added a result set mapping set by clicking Add, provide a name for the result. Depending on the result set type, you must use specific result names.

If the result set type is Single row, you can use either the name of a column returned by the query or the number that represents the position of a column in the column list of a column returned by the query.

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

Variable Name
Map the result set to a variable by selecting a variable or click <New variable...> to add a new variable by using the Add Variable dialog box.

Add
Click to add a result set mapping.

Remove
Select a result set mapping in the list and then click Remove.

Parameters in the Execute SQL Task

SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. In Integration Services, the Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.

Note

You can use parameters in an Execute SQL task only if the data provider supports them.

Parameters in SQL commands, including queries and stored procedures, are mapped to user-defined variables that are created within the scope of the Execute SQL task, a parent container, or within the scope of the package. The values of variables can be set at design time or populated dynamically at run time. You can also map parameters to system variables. For more information, see Integration Services (SSIS) Variables and System Variables.

However, working with parameters and return codes in an Execute SQL task is more than just knowing what parameter types the task supports and how these parameters will be mapped. There are additional usage requirements and guidelines to successfully use parameters and return codes in the Execute SQL task. The remainder of this topic covers these usage requirements and guidelines:

Parameter names and markers

Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. For example, the ADO.NET connection manager type requires that the SQL command uses a parameter marker in the format @varParameter, whereas OLE DB connection type requires the question mark (?) parameter marker.

The names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type. For example, the ADO.NET connection manager type uses a user-defined name with a @ prefix, whereas the OLE DB connection manager type requires that you use the numeric value of a 0-based ordinal as the parameter name.

The following table summarizes the requirements for SQL commands for the connection manager types that the Execute SQL task can use.

Connection type Parameter marker Parameter name Example SQL command
ADO ? Param1, Param2, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET @<parameter name> @<parameter name> SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC ? 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL and OLE DB ? 0, 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Use parameters with ADO.NET and ADO Connection Managers

ADO.NET and ADO connection managers have specific requirements for SQL commands that use parameters:

  • ADO.NET connection managers require that the SQL command use parameter names as parameter markers. This means that variables can be mapped directly to parameters. For example, the variable @varName is mapped to the parameter named @parName and provides a value to the parameter @parName.

  • ADO connection managers require that the SQL command use question marks (?) as parameter markers. However, you can use any user-defined name, except for integer values, as parameter names.

To provide values to parameters, variables are mapped to parameter names. Then, the Execute SQL task uses the ordinal value of the parameter name in the parameter list to load values from variables to parameters.

Use parameters with EXCEL, ODBC, and OLE DB Connection Managers

EXCEL, ODBC, and OLE DB connection managers require that the SQL command use question marks (?) as parameter markers and 0-based or 1-based numeric values as parameter names. If the Execute SQL task uses the ODBC connection manager, the parameter name that maps to the first parameter in the query is named 1; otherwise, the parameter is named 0. For subsequent parameters, the numeric value of the parameter name indicates the parameter in the SQL command that the parameter name maps to. For example, the parameter named 3 maps to the third parameter, which is represented by the third question mark (?) in the SQL command.

To provide values to parameters, variables are mapped to parameter names and the Execute SQL task uses the ordinal value of the parameter name to load values from variables to parameters.

Depending on the provider that the connection manager uses, some OLE DB data types may not be supported. For example, the Excel driver recognizes only a limited set of data types. For more information about the behavior of the Jet provider with the Excel driver, see Excel Source.

Use parameters with OLE DB Connection Managers

When the Execute SQL task uses the OLE DB connection manager, the BypassPrepare property of the task is available. You should set this property to true if the Execute SQL task uses SQL statements with parameters.

When you use an OLE DB connection manager, you cannot use parameterized subqueries because the Execute SQL Task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.

Use parameters with date and time data types

Use date and time parameters with ADO.NET and ADO Connection Managers

When reading data of the SQL Server types, time and datetimeoffset, an Execute SQL task that uses either an ADO.NET or ADO connection manager has the following additional requirements:

  • For time data, an ADO.NET connection manager requires this data to be stored in a parameter whose parameter type is Input or Output, and whose data type is string.

  • For datetimeoffset data, an ADO.NET connection manager requires this data to be stored in one of the following parameters:

    • A parameter whose parameter type is Input and whose data type is string.

    • A parameter whose parameter type is Output or ReturnValue, and whose data type is datetimeoffset, string, or datetime2. If you select a parameter whose data type is either string or datetime2, Integration Services converts the data to either string or datetime2.

  • An ADO connection manager requires that either time or datetimeoffset data be stored in a parameter whose parameter type is Input or Output, and whose data type is adVarWchar.

For more information about SQL Server data types and how they map to Integration Services data types, see Data Types (Transact-SQL) and Integration Services Data Types.

Use date and time parameters with OLE DB Connection Managers

When using an OLE DB connection manager, an Execute SQL task has specific storage requirements for data of the SQL Server data types, date, time, datetime, datetime2, and datetimeoffset. You must store this data in one of the following parameter types:

  • An input parameter of the NVARCHAR data type.

  • An output parameter of with the appropriate data type, as listed in the following table.

    Output parameter type Date data type
    DBDATE date
    DBTIME2 time
    DBTIMESTAMP datetime, datetime2
    DBTIMESTAMPOFFSET datetimeoffset

If the data is not stored in the appropriate input or output parameter, the package fails.

Use date and time parameters with ODBC Connection Managers

When using an ODBC connection manager, an Execute SQL task has specific storage requirements for data with one of the SQL Server data types, date, time, datetime, datetime2, or datetimeoffset. You must store this data in one of the following parameter types:

  • An input parameter of the SQL_WVARCHAR data type

  • An output parameter with the appropriate data type, as listed in the following table.

    Output parameter type Date data type
    SQL_DATE date
    SQL_SS_TIME2 time
    SQL_TYPE_TIMESTAMP

    -or-

    SQL_TIMESTAMP    		 datetime, datetime2
    SQL_SS_TIMESTAMPOFFSET datetimeoffset

If the data is not stored in the appropriate input or output parameter, the package fails.

Use parameters in WHERE clauses

SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for a SQL command. Parameters provide the filter values in the WHERE clauses.

You can use parameter markers to dynamically provide parameter values. The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses.

The following table lists examples of the SELECT command by connection manager type. The INSERT, UPDATE, and DELETE statements are similar. The examples use SELECT to return products from the Product table in AdventureWorks2022 that have a ProductID greater than and less than the values specified by two parameters.

Connection type SELECT syntax
EXCEL, ODBC, and OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

The examples would require parameters that have the following names:

  • The EXCEL and OLED DB connection managers use the parameter names 0 and 1. The ODBC connection type uses 1 and 2.

  • The ADO connection type could use any two parameter names, such as Param1 and Param2, but the parameters must be mapped by their ordinal position in the parameter list.

  • The ADO.NET connection type uses the parameter names @parmMinProductID and @parmMaxProductID.

Use parameters with stored procedures

SQL commands that run stored procedures can also use parameter mapping. The rules for how to use parameter markers and parameter names depends on the type of connection manager that the Execute SQL uses, just like the rules for parameterized queries.

The following table lists examples of the EXEC command by connection manager type. The examples run the uspGetBillOfMaterials stored procedure in AdventureWorks2022. The stored procedure uses the @StartProductID and @CheckDate input parameters.

Connection type EXEC syntax
EXCEL and OLEDB EXEC uspGetBillOfMaterials ?, ?
ODBC {call uspGetBillOfMaterials(?, ?)}

For more information about ODBC call syntax, see the topic, Procedure Parameters, in the ODBC Programmer's Reference in the MSDN Library.
ADO If IsQueryStoredProcedure is set to False, EXEC uspGetBillOfMaterials ?, ?

If IsQueryStoredProcedure is set to True, uspGetBillOfMaterials
ADO.NET If IsQueryStoredProcedure is set to False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

If IsQueryStoredProcedure is set to True, uspGetBillOfMaterials

To use output parameters, the syntax requires that the OUTPUT keyword follow each parameter marker. For example, the following output parameter syntax is correct: EXEC myStoredProcedure ? OUTPUT.

For more information about using input and output parameters with Transact-SQL stored procedures, see EXECUTE (Transact-SQL).

Map query parameters to variables

This section describes how to use a parameterized SQL statement in the Execute SQL task and create mappings between variables and the parameters in the SQL statement.

  1. In SQL Server Data Tools (SSDT), open the Integration Services package you want to work with.

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

  5. Double-click the Execute SQL task.

  6. Provide a parameterized SQL command in one of the following ways:

    • Use direct input and type the SQL command in the SQLStatement property.

    • Use direct input, click Build Query, and then create a SQL command using the graphical tools that the Query Builder provides.

    • Use a file connection and then reference the file that contains the SQL command.

    • Use a variable and then reference the variable that contains the SQL command.

    The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.

    Connection type Parameter marker
    ADO ?
    ADO.NET and SQLMOBILE @<parameter name>
    ODBC ?
    EXCEL and OLE DB ?

    The following table lists examples of the SELECT command by connection manager type. Parameters provide the filter values in the WHERE clauses. The examples use SELECT to return products from the Product table in AdventureWorks2022 that have a ProductID greater than and less than the values specified by two parameters.

    Connection type SELECT syntax
    EXCEL, ODBC, and OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    ADO SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

  7. Click Parameter Mapping.

  8. To add a parameter mapping, click Add.

  9. Provide a name in the Parameter Name box.

    The parameter names that you use depend on the connection type that the Execute SQL task uses.

    Connection type Parameter name
    ADO Param1, Param2, ...
    ADO.NET and SQLMOBILE @<parameter name>
    ODBC 1, 2, 3, ...
    EXCEL and OLE DB 0, 1, 2, 3, ...

  10. From the Variable Name list, select a variable. For more information, see Add, Delete, Change Scope of User-Defined Variable in a Package.

  11. In the Direction list, specify if the parameter is an input, an output, or a return value.

  12. In the Data Type list, set the data type of the parameter.

    Important

    The data type of the parameter must be compatible with the data type of the variable.

  13. Repeat steps 8 through 11 for each parameter in the SQL statement.

    Important

    The order of parameter mappings must be the same as the order in which the parameters appear in the SQL statement.

  14. Click OK.

Get the values of return codes

A stored procedure can return an integer value, called a return code, to indicate the execution status of a procedure. To implement return codes in the Execute SQL task, you use parameters of the ReturnValue type.

The following table lists by connection type some examples of EXEC commands that implement return codes. All examples use an input parameter. The rules for how to use parameter markers and parameter names are the same for all parameter types-Input, Output, and ReturnValue.

Some syntax does not support parameter literals. In that case, you must provide the parameter value by using a variable.

Connection type EXEC syntax
EXCEL and OLEDB EXEC ? = myStoredProcedure 1
ODBC {? = call myStoredProcedure(1)}

For more information about ODBC call syntax, see the topic, Procedure Parameters, in the ODBC Programmer's Reference in the MSDN Library.
ADO If IsQueryStoreProcedure is set to False, EXEC ? = myStoredProcedure 1

If IsQueryStoreProcedure is set to True, myStoredProcedure
ADO.NET Set IsQueryStoreProcedure is set to True.

myStoredProcedure

In the syntax shown in the previous table, the Execute SQL task uses the Direct Input source type to run the stored procedure. The Execute SQL task can also use the File Connection source type to run a stored procedure. Regardless of whether the Execute SQL task uses the Direct Input or File Connection source type, use a parameter of the ReturnValue type to implement the return code.

For more information about using return codes with Transact-SQL stored procedures, see RETURN (Transact-SQL).

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:

Specify 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.

Populate 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. When you set the TypeConversionMode property to Allowed, the Execute SQL Task will attempt to convert output parameter and query results to the data type of the variable the results are assigned to.

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 use a Foreach Loop container to extract the table row values that are stored in the Object variable into package variables, and then use a Script Task to write the data stored in packages variables to a file. For a demonstration on how to do this using a Foreach Loop container and a Script Task.

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.

You can use a Script task to access the System.Data.DataSet object, as shown in the following example.

Dim dt As Data.DataTable

Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)
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, non-string values that the SQL statement returns are converted to strings when the following conditions are met:

  • The TypeConversionMode property is set to true. You set the property value in the Properties window or by using the Execute SQL Task Editor.

  • The conversion will not result in data truncation.

Map result sets to variables in an Execute SQL Task

This section 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.

  1. In SQL Server Data Tools (SSDT), 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 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 Add, Delete, Change Scope of User-Defined Variable in a Package.

  10. 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.

  11. Click OK.

Troubleshoot the Execute SQL task

You can log the calls that the Execute SQL task makes to external data providers. You can use this logging capability to troubleshoot the SQL commands that the Execute SQL task runs. To log the calls that the Execute SQL task makes to external data providers, enable package logging and select the Diagnostic event at the package level. For more information, see Troubleshooting Tools for Package Execution.

Sometimes a SQL command or stored procedure returns multiple result sets. These result sets include not only rowsets that are the result of SELECT queries, but single values that are the result of errors of RAISERROR or PRINT statements. Whether the task ignores errors in result sets that occur after the first result set depends on the type of connection manager that is used:

  • When you use OLE DB and ADO connection managers, the task ignores the result sets that occur after the first result set. Therefore, with these connection managers, the task ignores an error returned by a SQL command or a stored procedure when the error is not part of the first result set.

  • When you use ODBC and ADO.NET connection managers, the task does not ignore result sets that occur after the first result set. With these connection managers, the task will fail with an error when a result set other than the first result set contains an error.

Custom Log Entries

The following table describes the custom log entry for the Execute SQL task. For more information, see Integration Services (SSIS) Logging.

Log entry Description
ExecuteSQLExecutingQuery Provides information about the execution phases of the SQL statement. Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. The log entry for the prepare phase includes the SQL statement that the task uses.