Sunday, April 17, 2016

SSIS - Object Types Variable in Execute SQL Task

What are Object Types Variable in SSIS?
In SSIS, Object Types variables are very generic object which are used to store the dataset result for the further processing. We can use them in the various SSIS components to achieve the requirements. They are used to store the output values from a relational query/stored procedure within Execute SQL Task component and can be used as an ADO recordset within a For Each loop container to perform iterative logic. They can be used in Script Task component to implement the business logic.
Object Types Variable in Execute SQL Task
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 to pass the input parameter and set the output in the Object variable –

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:

In the variable window, we are using two variables. One variable will be used as the input parameter which has integer data type. Another one will be used as the output parameter and must have Object Typed data type. 
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:


Now, we have set the input parameter in the parameter mapping. To pull the value from the output, we need to set the Result Set as given below: 
We need to do following things-

  1. Set the default Result Name as 0 (Zero) - (In Green background)
  2. Set the Variable Name as the defined Object Type variable. In our case, we have declared it as outResult- (In Green background)
Now, click on the Okay Button and execute the Execute SQL Task. It will execute without any error as given below-
By the help of Execute SQL Task, we have learnt that -
  1. How to pass the input parameters to the SQL Statements/Stored Procedures.
  2. How to set the result output into the Object Typed Variables.
  3. SSIS - Reading Custom Objects Variables
Now, we will learn another feathers of the Execute SQL Task.

No comments:

Post a Comment

Popular Posts