Hello friends, today I am going to talk about “Foreach Loop” container of SSIS. I have picked this topic as one of my blog reader asked me to write articles on different types of enumerators present in “Foreach Loop” container. There are 7 types of enumerators present in “Foreach Loop” container in SSIS 2008 version and I have already explained “File enumerator” in my earlier articles. Today I am going to talk about “ADO enumerator” in this article.
ADO Enumerator:- ADO enumerator is used to loop through on a dataset or record set returned by SQL statement. I am going to take a simple example here and you can explore further. In case if you have any doubt, you can reach out to me through my email id. Lets execute a SQL statement first that will return a result set. For that purpose, we need “Execute SQL Task”. We will drag and drop “Execute SQL Task” in control flow region as shown in the below screenshot.
To configure “Execute SQL Task” we will follow below navigation path.
Right Click on the “Excute SQL Task”àClick on “Edit Menu”à Bring a new screen as shown below.
We will specify the “Connection” (for more info read my earlier posts) and set the “ResultSet” property to “Full result set”. Now we need to specify the query that will be executed and get the result set.To specify the query, we will click on small the button (encircled by red color) that will bring a new window as shown in the below screenshot. In that window, we will write the SQL statement that will fetch the result set.
We will click on “OK” button to close this window. After closing the window, we will be back to the “Execute SQL Task Editor” window, there we will select the “Result Set” tab present at the upper left side that will bring a new layout in front of us(as shown in the below screenshot).
Now we will click on “Add” button that will add a row in the grid as shown in the above window. We will create a variable with data type “Object” that will hold the result set returned by the SQL statement. To create the variable we will “Click” on the combo box underneath the label “Variable Name” and click on “New variable” menu as show in the below screenshot.
Clicking on the “New variable” menu will bring a new screen as shown in the below screenshot.
We will give a relevant name to the variable, I have named it as “ResultSet” and value type would be “Object”. Clicking on “OK” will close the current screen and we will be back to the previous screen.
Now we will give the name to the result set, under the label “Result Name”, I have renamed the name as “0”. “Result Name” will be zero for “Full ResultSet” and “XML resultset”. We are done with “Execute SQL Task”. Click on “OK” button to close the “Execute SQL Task Editor”.
Next step would be to create three variables, we will create the variables by following below navigation path.
Right click on the control flow regionàclick on Variables Menuàthat will bring the variable creation screen as shown in the below screenshot.
To define variables, we have to click on the button(encircled with red color), I have defined three variables, Eid, Name and Dept and defined their data types as well. Now we will add “Foreach Loop” container and a script task to our package as shown in the below screenshot.
To configure the “Foreach Loop” container, we will right click on it and click on the “Edit” menu that will bring a new screen in front of us( as shown in the below screenshot).
We will select the enumerator type as “Forach ADO Enumerator” and for ADO object source variable, we will select the “ResultSet” variable that holds the result set returned by SQL statement. Now we will select the “Variable Mappings” tab(upper left side of the window), that will bring a new layout in front of us(refer the below screenshot for your reference).
I have mapped all the variables with indexes(1,2 and 3) respectively.
Important Note:- Please pay attention while mapping the variables to the indexes , we need to understand why I have mapped “Eid” with 0(why not with 1 or 2). The logic behind the mapping is that my select query is returning the result in the below sequence.
EidàNameàDept (I have written “select * from Emp_detail” and the sequence of columns in table “Emp_detail” is Eid, name and then dept) to avoid confusion we should use select statement with column list and variables will be mapped according to the sequence of the columns present in the select query.
Next step will be to configure the script task, to configure the script task we will right click on the script task and click on “Edit” menu that will bring a new screen in front of us(as shown in the below screenshot).
We can see a small button (encircled with red color) in the above screen, we will click on the small button that will bring a another new screen in front of us(please refer below screenshot for reference).
We will select three variables from the opened screen (Eid, Name and Dept), these variables will be passed to script task. Clicking on “OK” button that will close the “Select variable” Screen and we will be back to the earlier screen.
We will click on the “Edit Script” button( encircled with red color) that will open the “visual studio editor” as shown in the below screenshot.
In the main method we will write the below code.
MessageBox.Show(Dts.Variables[“Eid”].Value.ToString() + ‘ ‘ + Dts.Variables[“Name”].Value.ToString() +
‘ ‘ + Dts.Variables[“Dept”].Value.ToString());
We will save the changes and close the “Visual Studio editor” and will be back to the “Script task editor”, to close this window we will further click on “OK” button. Now are done with our package. Lets run it and see the results.
We can see in the above screenshot, we are getting a message box which is displaying the Eid=1, name=Neeraj and Dept=IT , as soon as we click on “OK” button of the message box, another message will come up with different (Eid, Name and dept) in this way ADO enumerator works in Foreach loop container. I hope after reading this article you will be able to use ADO enumerator in your SSIS package. Please feel free to ask any question related to this post.
2,561 total views, 1 views today