Mapping of Execute SQL Task parameters and variables

Execute SQL task can execute a SQL query statement or stored procedure (SP) with parameters and assign values to the parameters through the SSIS variable (Variable). For different connection Manager, you need to use a different symbol (Parameter marker) in the task to mark a parameter, and set the parameter name (mapping name) in Parameter Parameter.

In the Execute SQL Task editor, the interface for setting parameter mapping is as follows:

• VariableName: The name of the variable, the scope of the variable is divided into user and System, using :: to refer to variables in the scope
• Direction: The direction of the parameter, divided into input parameters (inputs), output parameters (outputs) and return values (returnvalue)
• Data type: parameter of the DataSource type, must be compatible with variable (Variable)
• Parameter name: Parameter name, different Connection manager, its value is different, for OLE DB Connection manager, use numeric value such as 0,1,2 to represent first, second parameter, third parameter and so on.
• Parameter Size: The default value is-1, which means that SSIS determines the length of the parameter. If the data type of the parameter is variable (varchar or varbinary), you must set the length of the parameter and allocate a sufficient length of space for the parameter value.

In Parameter mapping, Parameter marker and name are affected by the connection type, as in the following table:

Connection type	Parameter Marker	Parameter Name	Example SQL Command
Ado	?	PARAM1, Param2, ...	SELECT FirstName, LastName, Title from person.contact WHERE ContactID =?
ado	@<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 =?

1, Execute SQL query statement

In the actual development of the landlord, the most commonly used connection type is OLE DB, set the ConnectionType of the task to be a sqlstatement attribute, and use it to mark a parameter? Called a parameter marker (Parameter Marker).

Select ID,C1  from dbo.dt_test where c1=?

Assign a value to a parameter using an SSIS variable (Variable), Parameter name must be a number such as 0,1,2

2, execute SP, set the direction of the parameter

When executing an SP with Execute SQL task, the direction property of the parameter map is represented by Input,output and returnvalue, respectively: input parameters, output parameters, and SP return values

Declare @return_value int Declare @output_var int Declare @input_var intSet @input_var=1
exec @return_value=Dbo.usp_test@para1=@input_var,@para2= @output_varOutput

Using OLE DB Connection Manager, using? Represents a parameter,

To set the parameter mappings in Parameter Mapping tab:

• The first parameter is the return value of the SP, Direction select Returnvalue,parameter Name is 0;
• The second parameter is the SP input parameter, Direction select Input,parameter Name is 1;
• The third parameter is the SP's output parameter, Direction select Output,parameter Name is 2;

Reference Documentation:

SQL Server Sp2–what ' s new for SSIS

Map Query Parameters to Variables in an Execute SQL Task

Parameters and Return Codes in the Execute SQL Task

Mapping of Execute SQL Task parameters and variables