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.
No comments:
Post a Comment