 Hello, welcome to SSUnited, Susie in this side and this is continuation of SSIS tutorial. So today we gonna discuss about for each loop ADO enumerator. In our last video we have discussed about the file enumerator. So before going forward, if you haven't watched last video of this video series. So I would strongly recommend to watch that video. So let's start with ADO enumerator. So what ADO enumerator does? Basically ADO enumerator is used when data into the table and want to read data one by one and want to perform some action on it. Then we can use ADO enumerator or we can also say that it's used to read data from object type variable. So what it mean? As I have two case study, in our first case study, we wanna read data from employees table and display one by one. So first of all we need to load data from employee table to an object type variable in SSIS. Then we can use for each loop container with ADO enumerator that would read data from object type variable and display according to our requirement. So before going SSDT, let's have a look of SQL server table. So this is our table as we can see it has 13 rows and it has 4 columns. So we want to read data one by one. So go to on SSDT and here I want to add one more package. Then I wish to rename this package with for each loop and this is for ADO enumerator. Now I need to use an execute SQL task because it will pick data from employee table and load into object type variable. So double click on this execute SQL task and here I need to make the connection. So we can click on new connection. As I have already made the connection, so I wanna use that one click on OK. Here we need to specify our selected statement. As I have already written the selected statement, so I need to copy this and go to on SSDT and paste it here. Now click on OK. Then inside this result set, we want full result set. Go to on result set and here we need to add a result set and this result set should be object type. So go to on variable name and here we need to create a new variable. Data type of this object should be object and this is our object type. So we can use object data, now click on OK again OK. Basically execute SQL task will be executed and it will load data from table to object type variable. Now I need to use for each loop container and we need to connect with execute SQL task and here we need to configure our for each loop container. Now double click on this for each loop container, go to on collections and here we have enumerator types. So I need to use ADO enumerator this time. Here it will accept object type variable. So I can select our object type variable because this variable will hold the data once our package will be executed. Now go to on variable mappings and here we need to specify those columns which we are getting from database and we want to use. So here as we can see we are having four columns. First is employee ID, employee name, salary and department. So employee ID and salary and department ID is our integer data types and employee name is our worker data type. So here I need to create four new variables and this is for employee ID. So I can use as employee ID and it should be integer. So I can use integer 32. Here we need to specify default value. So I want to use 0, now click on OK as index 0 because we are getting first column. So it should be index 0, for employee name index would be 1, for salary 2, department ID 3. Now go to on SSGT and create three more variables and this is for employee name. So I can use as employee name. It should be string type, now click on OK. So next variable should be our salary type. So we can use as salary and it should be integer. So I can use integer 32, default value would be 0, click on OK. Here I need to create one more variable and this is for department ID. So I can use department ID and this is our integer type. So I can use integer 32 and default value would be 0. Now click on OK. As we can see index 0, 1, 2, 3. So we have mapped our variable successfully, now click on OK. So finally we have done our for each loop container. Now we want to read data. So how we can read the data? I have to use a script task inside this for each loop container. Now double click on this script task, read only variable. We need to select the variables which we have created. So our first department ID, employee ID, employee name and next variable should be our salary. So this is salary, now click on OK. Then we need to click on edit script. So here we want to write code to display the output. So I want to use messagebox.so. Then I need to specify dts.varables. I need to specify variable names. So these are the variable names now value.toStream. I want to concatenate ID. So I can use ID and we are getting ID from this employee ID. So I can remove other variables. So it will display the ID. Next I want to display employee name. So we can use EMP name. So we are getting employee name from our employee variable. So I need to use dts.varables and this time we want to get values from employee name variable. So this is our employee name variable. Now click on dot value.toStream. So we have mapped employee ID and employee name. Next I want to add our salary. We can use salary dts.varables and here I need to specify salary variable. So this is our salary variable. Now I can use dot value.toStream. And last is department ID. So I can use department ID and this is also getting from variable. So I can use dts.varables and here I need to specify department ID variable. So this is our department ID variable. Now I need to click on value.toStream. We have done our code. Now we can close it and click on OK. I can save it and click on start. So here as we can see if the employee ID is 1 employee name is Zohan, salary and department ID. So we are getting all the data. Now click on OK. This is for ID 2, ID 3, 4, 5, 6. So it will display all the records until employee ID 13. So package executed successfully. I can stop this package. Now I think we have understand how we can use for each loop container. And this is our basic case study. In our second case study we want to display data from employee table and it would be department wise. So here I want to filter out on the behalf of department ID. Go to on variables and here I want to create one more variable and this is for department ID value and it should be integer and I want to initialize default value as 1. I can close it. Double click on this execute SQL task. Go to on select statement and here I need to write where department ID should be close to question mark. Then this parameter will be mapped in parameter mapping as I have discussed when we have discussed about the execute SQL task. Go to on parameter mapping. Click on add and here in variable. So we want to get values from department ID value. Then it should be numeric type. Then here we need to specify parameter index. So that would be 0 because we have only one parameter and size is minus 1. That is your standard size. Now click on OK. Save this package and click on start. So this time it will reflect only those records where department ID is 1. As we can see we are getting 1 department ID 1 1. So our package executed successfully. I hope you have understand how we can use ADO illuminator. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.