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 –



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- 
  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.


  1. The instruments which construct the establishment are Mathematical devices and computational apparatuses. ExcelR Data Science Courses

  2. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

    Data Science In Banglore With Placements
    Data Science Course In Bangalore
    Data Science Training In Bangalore
    Best Data Science Courses In Bangalore
    Data Science Institute In Bangalore

    Thank you..

  3. This is exactly the information I'm looking for, I couldn't have asked for a simpler read with great tips like this... Thanks! data science course in surat

  4. Two full thumbs up for this magneficent article of yours. I've really enjoyed reading this article today and I think this might be one of the best article that I've read yet. Please, keep this work going on in the same quality. data science course

  5. You re in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject! data scientist course

  6. I will truly value the essayist's decision for picking this magnificent article fitting to my matter.Here is profound depiction about the article matter which helped me more. digital marketing training malaysia


Popular Posts