Execute SQL Task

The Execute SQL task is used to execute an SQL Statement from the SSIS package.


1] Drag and drop and "Execute SQL Task" object from tool bar.

2] Enter an appropriate name for the task. Select a connection Type and a connection object. If no connection has been created yet, you can create a connection here by clicking on "New Connection" in the Connection value box
3] Select a Result Type for your Task.
a] If you do not expect any result values back from DB, Select "None".

b] If you want a single value or multiple values, Select "Single Row".

c] If you expect rows of data or a dataset , Select "Full Resultset".


4] Select a SQL Source Type. It can be entered directly, or can be stored in a variable or can be stored in some file.


5] Enter a command by clicking on the ellipse that comes up on clicking in the SQL Command value box.

6] If your command has parameters, use "?" character in place where value is to be replaced into the command. If there are multiple parameters, use "," to separate the parameter values.

7] Enter an execution command and click on "OK".

8] If your command has parameters, move to Parameters tab.

9] Click on "Add" button to add a new Parameter. Replace the ParameterName column to indicate which variable takes which position in the command.Use 0,1,2..... to indicate the first,second,third variable and so on.
10] Select the variable that holds the Parameter value in the Variablename column. If no varaible has been created, you can use the "New Variable" option by clicking on the dropdown.

11] So the command here is equivalent to :

"Exec Sp_AddTwoValues @value1=Variable1, @value2 = Variable2"

12] If you do expect any result values, move to Results tab.

a] If you want a single value or multiple values, click on Add and use the same logic as used on Parameter Screen.

b] If you expect rows of data or a dataset (i.e. with "Full Resultset" selected in "Results Type" option) Add a single variable here of datatype"Object".

13] However, if no values are fetched into the result variable, "Execute SQL task" throws error.This is one of the bugs in SSIS :)