 Hello, welcome to SSUnitex, who is in this side and this is continuation of SEO Data Factory interview questions and answers. So in this video, we are going to see how we can load the data from multiple Excel files and those files is also having multiple Excel seats from your blob storage to the multiple SQL tables based on the Excel seat name. So this will be the long video. So I would say don't skip this video because you will be understanding multiple concepts here. You will be able to answer those in your interviews. So one of my friend has asked this to record a video. So I'm here for the solution of this question. So let me quickly go and show you the file. So total we are having these four files and I have already placed all these four files into the blob storage and these Excel files are having two seats. One is the employee seat and second is the department seat. Inside the SQL we are having two tables. One is the employee table and second is the department table. So all the data from all these Excel files should be going to load inside the employee and department table. So let me quickly go inside the browser and here you can see we are under the input container and here I have already placed all these files. So let me quickly go inside the SEO Data Factory and here let me add a new pipeline. And I am calling this pipeline as one read Excel files. So something like this. Now so first as I told you we have to loop through with all these files and we'll get the file names from here. So here we can use the get metadata activity and this get metadata activity will be reading all these Excel file names from this input container. Here we have to go inside the setting and add a new dataset. And here we can simply go and check this as your blob storage click on continue. Excel is the file so click on continue. Here I am going to leave this as Excel 3. Link service we have already created for the input blob storage. We can go and select the input folder and under that we have the sales folder. So under the input we can see the sales folder and sales folder are containing all these files. So let me click on OK. Here I have not selected any files because we want all these files. So here by default I am going to specify the index as 0 or you can also add the name of the file. So I am going with the index first as header we can mark this and click on OK. So what it will be returning it will be returning all these four files. But we have to add a field list as child item. Now let me debug it. So once we will be executing it we should be seeing the output as four Excel files. So it got executed we can check the output. So inside the output we should be having all these four files. So that looks OK. Next we have to use the for each activity. So inside the for each activity it will be processing the files one by one. So if we can go in the setting and the item property we can select the get metadata child items click on OK. So it will be looped through four times. So inside the activity first we can see we are unable to again use the for each activity. Why it's required because the first for each activity will be looping on the file level. And second for each activity are required to loop through with the seat level. So that is the major issue we cannot use the for each under the for each. So in interview interviewer generally ask this question. You should be answer this question after watching this video. So what is the alternate way by which we can achieve it. So before going to do that let me quickly go inside the SSMS and try to create few of the required tables and the configuration. So as we have seen in the earlier videos we cannot directly read the seat names inside the Azure data factory. We have to create any conflict table or we can create any Excel file by which we will be keeping the information of the seat name. So here I'm going to create this conflict table and it is having two columns file name and Excel seat name. So let me try to execute for creating this table. So your table is created successfully. Next I'm going to insert the conflict details here. So as we have four Excel files first is India Pakistan US and UK and two Excel seats. So employee and department. So let me try to execute this script for inserting the data. So as eight rows has been inserted. So here we are required to create this store procedure and this store procedure will be taking one input parameter as file name and will be returning the seat name based on that file name. So we are simply selecting the data from the sales table and filtering based on the input parameter of the file name and will return the seat name. So this is very straightforward store procedure. So I'm creating this as well. Let me recap. First we have created the table then we have inserted the data into the table and we have created this store procedure for fetching the seat names from the file name. Now let me quickly go inside the browser and here as we are restricted to use the for each activity here. So let me quickly go and try to create another pipeline and this pipeline I'm going to call two. So what we have to do here we are required to use the lookup activity. So what lookup activity will do this lookup activity will be taking as an input parameter of the file name and will return the seat names from using that store procedure. So first we are required to create one of the parameter and let me call this parameter as file name. Now we can go inside the lookup activity. Here we can go into the general and here source. So let me create a new data set here. And this time we should be getting from the Azure SQL database click on continue. And after that here we can select the link service and then we can see the table name. So here we are not required to select anything. So let me go with the none and then we can click on OK. Here we can see the option for the store procedure. So as I told you we have already created the store procedure. So we can simply select that store procedure. So this is the get seat name store procedure. We can import the parameter so it will be asking one input parameter as the file name and the file name parameter we have already created. So we can simply pass this over here. Now once we will be executing it it will be asking this file name parameter. So if we can go here and in this query let me copy this. We'll pass it here click on OK. So it will be executing and in the output it will be returning two rows one for the employee and second for the department as it is having two seats under that. So the same thing should be returning over here as well. So we can wait so we can check the output. So as it is only returning first row because in the lookup activity we have selected this first row. So let me unselect and try to debug it again. Click on OK. So as we can see the output so it is having total two one is the employee and second is the department. So we have done with the lookup activity. Next here we can use the for each activity. So what this for each activity will be doing it will be looping through employee and department Excel file. So go to the setting and here inside the items we can simply say like the lookup value array. So it will be looping through for the employee and department. So under the for each activity we can use the if condition activity. So what if condition activity will do it will be checking your input parameter that we are passing from lookup activity to the for each activity. If that value is contains employee then that is the employee seat otherwise that is the department seat. So here inside the activity inside the expression here we can see this item dot seat name. So seat name is the actual property. So how we can say like this is the seat name simply let me go back to here and if you can see the output then we can see the property which is the seat name. So this property we are using there. So let me go there again. And here let me use the seat name property. So if this value contains employee. So we can simply say if this is employee then your condition will true. So it means we have to use the copied it activity two times one for the true blog and second for the false inside the true or blog. It will be copying to employee file and in case of the false it will be copy to department. So here we can simply use the copy data activity. And let me try to go and set up the source and sync. So your source is dynamic because the file name and the seat name both are coming at the runtime. So let me create a new data set and it should be pointing to the as your blog stores click on continue excel seat click on continue here let me select the link service and after that we can go and input then sales and we can click on OK without selecting any file. Here we can see the index so I am going to specify 0 by default but we will be making the change later click on OK. So it is going to create a new data set and this data set will be dynamic. Your file name will be getting from the parameter and your excel seat name will be getting from the forage activity. So simply click on this open and this file name as I told you it will be getting from the parameter. So here first let me try to create a new parameter and let me call this parameter as file name and here we can simply go and try to select the file name over here and the second parameter we have to create for the seat name. So this data set is dynamic and it will be asking the seat name and the file name as dynamic at runtime. So we can select the seat name click on OK. So once we made this change if we can go back to the pipeline. So here it is asking the file name and seat name. So file name is getting from the parameter that we have created on the pipeline level. So we can simply go inside the parameter and file name can get it from here. But the seat name that should be getting from the forage activity. So we can simply use the item and then we can use the seat name property. So from here we can get it. So we have supplied these two parameter values here. Inside the sync we can simply go and try to create a new data set and this should be pointing to errors equal database click on continue. We can select the link service then we can select the employee table. So here let me use the employee table and click on OK. So this data should be going to load into the employee table. Now let me quickly go inside the if condition activity and under the if condition activity on the false side again we can use the copy data activity. So this copy data activity will be loading the data into the department table. So as we have created the Excel for as a source so we can utilize that over here as well. But we can pass the file name so that should be coming from the parameter and the seat name that should be coming from the items dot seat name. So it is coming from here. Let me click on OK. So we have done the changes over here inside the sync we can create a new data set and this is pointing to as your SQL database and then we can select the link service then we can simply select the table. So I am going to use the department table. Let me click on OK. So we have done these two copy data activities. Now the only thing is remaining once we will be executing this. So it should be executed from the pipeline one. Remember we have created the pipeline one at the starting of this video. So what it is doing it is looping through with all the Excel files one by one. And inside this for each activity we can simply call the pipeline that we have created. So for that we can use the execute pipeline. So inside the execute pipeline we can go and here go to the settings and it will be invoke the pipeline which is two but it is asking one input parameter value that is the file name. So this file name value is coming from the for each activity. So whatever the value that we are getting we can simply pass it here. So that is the name property click on OK. So we have successfully configured everything. Let me try to execute it and we'll check the output. So we can click on this debug button. So it will start executing. We can go inside the systems and we can see employee and department table. As of now we should not be having any data as we can verify. We can see it should be going to start executing. So get metadata one is completed successfully. If you can go here and we'll see the get metadata one is returning all those four files and for each activity will be executing four times as the item count is four. And inside the for each activity we have execute pipeline activity. So execute pipeline activity will be also executing four times. So first is executed successfully. If you can go and check here then we can verify. So simply we can go and here we can see it got executed. So inside the lookup activity it is returning employee and seat name. The file name that we got from the pipeline one. Then for each is executing two times one for the employee and second for the department. Then if condition is executed twice one for the employee and second for the department then copy data one and copy data two got executed because copy data one for the employee and copy data two for the department. So we can go here and let me try to check the output. So how many got executed? So two files has been processed three files has been processed only one file is remaining. So all these got executed successfully. So we can simply go into the SSMS and inside the SSMS we can select these two tables. So as we could see so 20 rows are here. Similarly here we have eight rows so two rows for each file. So that's why we can see eight rows. So this is the way by which we can achieve it. Let me recap what we have done in this video. So first we have created two pipelines inside the first pipeline. We are simply looping through all the Excel files and passing inside the for each activity. And inside for each activity we are calling another pipeline. And the second pipeline so we have created one input parameter. So that input parameter will be the file name that we are receiving from the pipeline one. So first we have used the lookup activity and here we have used the store procedure that we have created for fetching the seed name. And after that all these seeds we are looping through inside the for each activity. Then inside the if condition we are simply checking if your file name contains employee or not. If that is containing employee then we are loading data into the employee table. Otherwise data will be loading into the department table. I know this is a little bit tricky but if you have still any doubt then you can watch this video again. And thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. See you in the next video.