SSIS – Read Object Variable in Foreach Loop Container

We are working on the object variables and understanding their behaviour in the various SSIS Controls. We are using Execute SQL Task to store value into object variable and also read object variable into Script Task. To understand the object outputs, we are using another most important component of SSIS called Foreach Loop Container. So, in this section we are going to use them to do the followings –

• Execute SQL Task - Load value into object variable
• Foreach Loop Container – Read value from object variable and assign into another variable
• Script Task – Read the assigned value from another variable

To understand the object variable behave in Foreach Loop Container, we are doing the following settings -

1. Use runtime data table to generate the value which will be stored in the object variable by the help of Execute SQL Task.

--- Use derived data table on the fly SELECT DEPTNAME,DEPTID FROM ( 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 )DT DEPTNAME DEPTID SALES & MARKETTING 101 ACCOUNT & FINANCE 102 INVENTORY 103 PRODUCTION 104

2. Use SQL Connection and two different types of variables such as object variable having Object data type to store multiple values and another string data typed variable to get the value in Foreach Loop Container. 

3. How assigned value is reading in Script Task 

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. 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- 
6.  
   
7. To pull the value from the output, we need to set the Result Set as given below: 
8.  
   
9. 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-
   
   
   

After setting the returned values in the Object variable called outResult, we need to read the value from the Object variable. For this, we need to add Foreach Loop Container and Script Task component from the SSIS Toolbox window and link it with Execute SQL Task component with the help of Precedence Constraint as given below:

Important Setting to real object variable in Foreach Loop Container and assigned read value into another variable to further user

1. Right click on the Foreach Loop Container and choose Edit which will open Foreach Loop Editor window. In Editor window, go to collection tab and set Foreach ADO Enumerator against Enumerators as given below- 
2.  
   
3. After set the Enumerators, set object variable in ADO Object source variable as above in point no 2.
4. and Enumerator mode should be checked as Rows in the first table as given above in point no 3.
5. Now, we need to set the variable mapping. For this, go to variable mapping and choose the local variable name inpDeptName to hold the value one by one from ADO Object source variable and set the index value =0 as given below: 
6.  
   
7. If your object variable is storing more than one column then this index should be increased in the incremental orders. We did all the important settings within Foreach Loop Editor. Now, click on the OK button to apply the changes in Foreach Loop Container.
8. We need to understand the enumerator functionality how does it read the value one by one form Object variable in Foreach Loop Container. So, we are using the must usable Script Task to show the alert having values.
9. Add Script Task from SSIS Toolbox. Right click on the Script Task and click on Edit which will open the Script Task Editor window. In Script Task Editor window, set User::inpDeptName against ReadWriteVariables because we have already assigned value within Foreach Loop Container as given below: 
   
   
   
10. After adding the desired variable, we need to click on the Edit Script button which is open Script VstaProjects window where just put the following code as given below: 

    public void Main()                                 {                 // TODO: Add your code here             Dts.TaskResult = (int)ScriptResults.Success;                                            // declare variable for string             string strMsg = string.Empty;                         // Set values into local variables             strMsg= Convert.ToString(Dts.Variables["User::inpDeptName"].Value);             // Alert message             MessageBox.Show(strMsg);                                 }

11. Now build the code in the Script VstaProjects window as given below: 
    
12. After successful execution code, close Script VstaProjects window. In Script Task Editor click on OK button.  
13. Wow, we have done all the necessary code and variables settings. It's time to just run the package. When our package goes to execute the Foreach Loop Container, it calls the script task which show the variable alert message one by one as given below: 
    
    
    
    
14. After clicking on the all the alert messages one by one, our package will be executed successfully as given below: 
    

Conclusion

Object Variables play very important role in the data processing program because they are able to hold the multiple values which can be handle another controls. Execute SQL Task can load the values in the object variables and Script Task and Foreach Loop Container are applicable to read the values from the object variables and make our package to meet the business requirement. So, use them wisely.