Friday, April 15, 2016

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.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands