SSIS - Use Variable in Execute SQL Task

As we know that Execute SQL Task is an extra ordinary component in Integration Services. We can use one or more variables in Execute SQL Task to pull the information from the database.  
To pull the values from database, we can use the SQL Statement or stored procedure with the parameters. To understand this functionality, we are using the below dataset –

SELECT 101 AS DEPTID, 'SALES & MARKETTING' AS DEPTNAME UNION SELECT 102 AS DEPTID, 'ACCOUNT & FINANCE' AS DEPTNAME UNION SELECT 103 AS DEPTID, 'INVENTORY' AS DEPTNAME UNION SELECT 104 AS DEPTID, 'PRODUCTION' AS DEPTNAME DEPTID DEPTNAME 101 SALES & MARKETTING 102 ACCOUNT & FINANCE 103 INVENTORY 104 PRODUCTION

Now, we need to declare a variable in SSIS development window and set the default value as given below:

After declaring the variables, we need to add Execute SQL Task in Control Flow Window from the SSIS Toolbox control as given below:

Now, we need to set the data connection and SQL Statement in the Execute SQL Task. For this, right click on the Execute SQL Task and click on Edit. This action will open the Execute SQL Task Editor window. We need to set the following things as given below in green colors- 

1. Set the Result Set as Full result set 
2. Connection  need to set the data source from connection manager 
3. SQL Statement, need to set the SQL statement or stored procedure with question mark (?) where ? is represented as parameter value to the data source. Enter SQL Query window, we can write the SQL query or stored procedure- 
4.  
   
5. After adding the SQL statement, it will be look like as given below-
   

After setting the above setting, we need to set the Parameter Mapping in the Execute SQL Task Editor window. For this, click on the add button and choose the parameter which needs to be passed as given below:

After doing all the stuffs, click on the OK button. Now, we have done all the steps to pass the variable value to the back end. Now, execute the Execute SQL Task to pull the requested values.

Now, we are able to pass the input values into the data source to pull the expected result with the help of Execute SQL Task. Now, we will learn, how to capture output result from the Execute SQL Task.