 Hello, welcome to SSUniTek Susil this side and this is continuation of Azure Data Factory interview questions and answers. So recently one of my subscriber has attended the interview. So this question was asked there. So in this question we are going to see how we can insert the folder name and the file count under that particular folder from the blog storage to the SQL Server table. So let me quickly go inside the browser. And here let me tell you about the requirement. So as we can see inside the input container we are having these many folders. If we can go inside those folders we can see the files. So the requirement is we want to insert the folder names from here and whatever the file available under that particular folder what will be the total count of that file. So as we can see under the sales we are having four file. So in the table we will be having sales in the first column and in the second column that will be the four. Let me quickly go inside the SSMS. So here we are required this table. So this table will be keeping the information for the folder name and the file count. So let me try to create this table first. Command completed successfully it means table is created. Next we are required to create one of the Strobe procedure. So this Strobe procedure will help us to insert the data into this particular table. So it will be asking two input parameter. First is the folder name and second is the file count. And here we are checking if the folder name is already available inside the table. Then we are going to update the file count. If the folder name is not available in the particular table then we are going to insert the folder name along with the total file count. So let me try to create this SP. Command completed successfully it means SP is created. As of now we don't have any data in this table. Let me quickly go inside the browser and here let me quickly jump to the Azure Data Factory. Here let me add a new pipeline. So first we have to get all these folders inside the get meta data activity. So we can simply use the get meta data activity and here we will be specifying the path that will be up to input location and after that we will ask for the child item. The folder is available under this that will be reflecting. So here we are required to select a data set or we can create a new data set. So let me click on new. Here I can search for the Azure Blob Stores click on continue. So inside the format we can select any one of the format. So I am going to go with the delimited text. Here we can select the link service that we have already created and here it is asking about the file path. So instead of specifying file path we can directly specify up to the input location and then we can see all these folders then we can ignore and click on OK. First row as header we can select this checkbox and click on OK. So this will be created and under this we can see the field list. So here we will see about the child items. So we can select the child item and try to execute it. So if we can see the output then we can see all those folders. So type is folder and the name that is the India Pakistan sales UK US. So all these folders are here. Now next we need to loop through with all these folders one by one and after that we will be checking whatever the files available. So we can use the for each activity. So this for each activity will be looping through one by one inside the get metadata activity. So under the setting under the items we can go and we can select the input as an get metadata one of the child items click on OK. Then we can go inside the activities. So under this first we are again required to use the get metadata activity. So those get metadata activity we will be using. So for example we are under this folder and here how many files are there. So we can use the get metadata activity of the child item. So that will be again getting all those child items. So here we can go and we can use the get metadata activity. And this get metadata activity we can go and simply inside the settings. We can select the data set. So let me create a new data set and this again will be pointing to as your blob stories. Here we can select the format click on continue. We can select the link service. Then we can specify the path. So under the input we will be making this as dynamic because these folder path we will be getting from the for each activity. So let me click on OK without selecting anything this directory we will make it as dynamic. So let me click on OK. So this data set will be created and let me click on this open. And here let me quickly go and create a new parameter and call this parameter as folder name. Inside the connections we can see directory here we can select and click on OK. So after making this change we can directly go inside the pipeline. And here it is asking about the folder name now. So this folder name we are getting from the for each activity. So till now we are good and here we can see the field list. So we can add a field list and child items because we want the child items here. Now let me try to debug it. So what it will be doing first it will be executing get meta data one. So it is start executing so get meta data one will be executing and once it will be executed total five folders will be here. So inside the for each activity it will be executed five times. And under that we have get meta data two. So get meta data two will be executed five times as we can see this for each activity item count is five. Now we can see get meta data two. So here we can see the name. So name is the file and the type we can see the file and the name of the folder we are getting by using the for each activity. So this is we have done. Just what we have to do here we are having the folder path that is inside the for each activity and then how many files are there by using the get meta data two of the child items we are getting that but we also required to use the total count. So what will be the total child items that we want. So we can achieve that by using two methods. First method we can use the for each activity again but for each activity cannot use directly here so how we can use it by alternative way we have seen in the last video. So we can add another pipeline and on that particular pipeline we can use the for each activity and we can call that pipeline here. So I am not going to use that method because we only want the total count. So here we can use the filter activity. So actually we are not going to do any filter kind of thing here. It will be going to get only the count. So how it will be getting let me go inside the settings here under the items. So here we can go to the activity output and here we will see about the get meta data two. So if we scroll down we can see get meta data two of the child items. Let me click on OK. So this is done inside the condition we are not going to filter anything. So simply we can use the equals and when one equals to one then we are going to get out. So always it will be true. So it will be filtering click on OK. Let me try to debug it and we will see about the output of the filter activity. So here we can see filter activity has been executed five times. So if you can see the output then we can see the item counts. So this items count is one and the filtered items count is one. So always these two values will be same. So let me click on this close and here simply what we can do we can use the stored procedure activity. So this stored procedure activity will be executed and as per the file name the file count will be inserting into the table and the file count we are getting by using this filter activity. So here simply we can go inside the settings. We can select the link service that we have already created. Then the store procedure name we can also specify the store procedure that we have created this one. And then we can click on this import of the parameters. So it is asking two parameters first is the file count and second is the file name. So the file count parameter we are getting by using the filter activity. So here under the activity output we can use the filter and after that we can use the items count because this property is holding actual total count. Let me click on OK. So we have done with the file count. Here we can use the folder name so we can directly get it from for each activity. Now let me click on OK. So we have done with all these let me go inside the pipeline and try to execute it. So once it will be executed your table should be having the data. If you can go and we will try to see as of now we should not be having any data. Once this will be executed we will see the data there. So we can wait until this will not be executed. So get a metadata one has been executed successfully now for each activity is start executing and we can also verify like the store procedure one is executed three times and it should be executing four times and five times. So all these got executed successfully. Let me go back inside the SSMS and we are seeing all these five rows with the total file count. If you want to execute this again then we can also do that. Let me quickly go and click on this debug. So it will be executing again but your table will not be changed. It will remain same because the file count those we have already inserted is having the same count. If we are going to add any new file then that count will be changed and will be updating as per the latest count of the file. So it is start executing we can wait until this will not be executed. As we can see it got executed successfully. If we can go and try to execute it then we will see it is having the same output. So I hope guys you have understand how we can achieve it. I know this is little bit complex. If it's not clear you can watch this video again. Thank you so much for watching this video. See you in the next video.