 Hello friends, welcome to SSUnitech, so see this side and this is continuation of SSIS tutorial. So today we gonna discuss about for each loop file enumerator. In our last video we had discussed about basics of for each loop container and types of enumerator. So before going forward, if you haven't watched part 10.1 of this video series, so I would strongly recommend to watch that video where you can understand about the dynamic connection manager. So let's start with file enumerator. So what is for each file enumerator? So it is used to loop through all the files present in a specified folder. It will also has an option to traverse the subfolders present into that specified folder. Like we have a folder and that contains many files and we want to load data from those files into SQL Server table. Then we can use it can be used to fetch full qualified file path or file name only with or without extension. So here we have a case study as we have four text files into a single folder and want to load data from those files into SQL Server table. So let's have a look of flat files. So this is our folder as we can see in this folder we have four files. So this is our first file and it has information for sales in India, then sales in Peru, then UK and then US. So we have total four files and we can see we have four columns in each file and first is customer ID, then item name, then quantity, then value. So we want to load data from these files into SQL Server table. So go to on SSMS and here as we can see we have this table customer sale table and we want to load data in this table from these files. So how we can achieve this? So go to on SSDT and here I would like to add one more package. So new SSIS package. Then I wish to rename this package for each loop and this is for file inverter. As we have discussed in our last video we have three types of containers. First is for loop container, for each loop container and sequence container. So I want to use for each loop container. Then double click on this for each loop container. Go to on collections and here we can see in this enumerator we have multiple options but we are going to deal with files. So we need to select for each file enumerator and here as we can see we have enumerator configurations. Here we need to select the folder path and then we need to select the file extension. So before configuring for each loop container let's create two variables. First for the folder path and second for the file name. So this is for folder path and it would contain the information of the folder. So this is our folder where our files exist. So I need to copy and inside this value we can paste that value. Then second for the file name. So I want to use file name and it should be string type. Now I can save it and we can close it. Now I need to double click on this for each loop container. Go to on collection tab and here I need to select file enumerator. So in this folder we need to specify the folder path. So we can copy it and we can paste over here. And here what type of files we want we can specify over here. Like we want only txt file so we can specify .txt. So here we have three options. First is name and extension, fully qualified name then name only. So we want name and extension. So it will retrieve name and extension. So we want to store name and extension into a variable. So this is our file name. So where we want to keep the information of file name with extension. Now we can save it and here we need to use a dataflow task inside this for each loop container. Now double click on this dataflow task. So we want to get data from flat file and want to load data into sqlsobatable. So we can drag and drop flat file source and per ADB destination. Now double click on this flat file source. So here we want to make connection any of these files. I am going to make connection with India. Click on open. Go to on columns. It looks good. Now click on ok again ok. Now we need to destination. Double click on this and click on new. As I have already made the connection so I am going to click on ok. Here we need to specify our table name. So we want to load data in customer sql table. Go to on mapping. Mapping succeed because our input column name and destination column names are same. Now click on ok. So we have done the mapping and this is our static mapping. So once we run this package our for each loop container will be executed 4 times because in this folder we are having 4 files. So it will be executed 4 times but data will be loaded only from sqlindia flat file because we made the connection only for India. Now we need to make this connection dynamic. So we can click on flat file connection manager. Then go to on expressions and here so here we have a property with connection string. Now go to on expressions and here we need to drag and drop folder path. Then we can specify plus and then file name. Now we can evaluate and we can see we are getting but the file name will be calculated at runtime. So it looks good click on ok again ok. We have done it. Now we can execute our package to check the output. Now we can click on start. As we can see package executed successfully. Now we can stop this package and go to on ssms. Now we can refresh this table. It should get 12 records. So we are getting 12 records because 3 records is available in each flat file. Now double click on this for each loop container and here go to on collections and here we can see we have an option for traverse subfolders. Once we select this checkbox and click on ok. So it will check on that folder which is available in this location. As we can see we are having sale Australia. So we are having same number of columns. It also have 3 records. Now I wanna execute this package. Once I execute this package then we can see our package thread because once we gonna calculate our connection string. So here first it will check the folder path. So our folder path will be this but this file is available inside this subfolder. So it is not going to check in this folder because we have hard code the value for the folder path. So this is the difference while we gonna use folder path plus file name and full qualified name. So here I gonna cancel and cancel. Double click on this go to on collections and this time we gonna use full qualified name. Go to on variable mapping. So in file name it will pick the folder path plus file name. I can click on ok. Now go to on flat file connection manager and here go to on expressions and here in this expression I need to remove this folder path because our folder path and file name will be keep in this file name variable. Now we can click on ok. Go to on ssms and here I would like to delete all these rows so I can delete and here I can see once we refresh this table then we are not having any data. Now once our package will be executed successfully then 15 rows should be available in our table. So I can click on start. So it looks good. Now stop this package and go to on ssms and refresh this table. So as we can see 15 rows inserted in this table. So this is the difference while we gonna use folder path plus file name and extension and full qualified name. Once we are having subfolders inside the specified folder and we want to load data from those files which is available on the subfolder then we cannot use folder path and file extension we need to use full qualified name. 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.