 Hello, welcome to SSUnitex, so she'll decide and this is continuation of Azure Data Factory interview questions and answers video series. So in this video we are going to see how we can copy the data from the multiple excel seats to the SQL table. So what does it mean? Let me quickly go and show you the scenario first. So we are having a excel file and under that excel file we are having multiple excel seats so that we can see India, then US, then UK. So all these are having similar kind of data but those are for that particular country. So as per the UK we can see the UK employee, similarly we can see the US employee and then we can see the India employee. I'm keeping all the data same except the country name. So we need to load this data inside the SQL Server tables. So as we can see it is having multiple seats so all the data from all those seats should be copied inside the SQL Server table. So how we can do that? So this was asked in one of the interview that was attended by one of my subscribers. So let me quickly go inside the Azure Data Factory and we'll try to see. So here inside the blob storage I have already uploaded this excel file that we can see employee.xlsx file and if we can go here so we don't have any direct option by which we can loop through the excel file seats. So we cannot directly do that. So the alternate way we have to follow for solving this problem I have created this config file. So inside this config file I am having all those excel seats. So if by next time we have received the file and those file is having four excel seats then we can simply go and add one more seat name here and it will be working for that particular seat as well. So either you can add inside the csv file or you can add inside the SQL Server table as an config table. So here I am keeping this inside this particular csv file. So now let me quickly go inside the data factory and try to add a new pipeline and we'll try to solve this problem. So first what we have to do we have to use the lookup activity and on that particular lookup activity we will be reading all these data. So as we can see it is having India, US and UK. So all these three data should be returning by that lookup activity. So let me quickly go and search for the lookup activity here and then we can go inside the settings and we can add a new data set here. So this should be pointing to the blob stories. So we can select the azure blob stories. Then we can see the delimited text because that is the csv file of the config one. So we can click on continue and here inside the link service we have already created this link service. So I am going to use the same. Then we can simply browse and we can go on that particular input location and after that we can simply select this config.csv file. Then we can click on OK. First row as header we have already seen like seed name is the first row. So we can select this checkbox and after that click on OK. So once we have created this let me try to debug it. So this should be going to return three rows in the output. So one for the India, UK and US. So if we can check the output of this here we can see the first row. So why it's returning first row let me cancel this and let me go inside the lookup activity. Here if we can see we have selected this checkbox for the first row. So that's why it is only returning India. But we are required to see all the data set. So we have to unselect this checkbox and let me try to debug it. So this time it should be going to return all those three rows. So we can wait until this will not be executed. So let me check the output of this. So as per the count we can see three but the seed name. So seed name property we can see India, US and we can see the UK. So what next we need to do we need to use the for each activity and that for each activity will be executing on the seed name property of the lookup activity. So that will be executed three times one for India, US and UK. So similarly here we can use the for each activity. So let me try to drag and drop the for each activity and let me connect with the lookup activity. So here what is the items by which we need to loop. So we need to loop through with the value array. So whatever the value array is available that should be going to executed on that level. Let me try to click on. Okay, let me show you the value array one more time. So if we can click on this output. So here we can see this value. So why it is array because we are having multiple values under that particular array. So that's why it is asking whatever the value is available in the array. So on those elements, the loop will be executing. Now after doing this, we can simply go in the for each activity and here we can simply use the copy data activity. So the copy data activity we have to set the source and sync but the source should be dynamic because the seed names we are getting from the for each activity. So let me try to go inside the source. So let me try to add a new data set here. And this is again our label inside the blob storage. So we can select it. And after that our source file, which is the actual file is the excel sx. So our source is in excel file. We can select click on continue. And after that we can simply select this link service. We can browse go to the input and we can select that particular employee file. And after that click on OK. So once we have done all these here, I'm going to use the worksheet mode as index and we are going to pick zero and first row as header. So I'm going to make this but we'll be going to change. So let me click on OK because we don't have any option by which we can make it as dynamic here. So after creating it, we can design this as an dynamic. So as we can see it's created, let me click on open. And here if we can go and here we can see the seed index. So either we can go with the seed index or the seed name. So I'm going with the seed name because those seed names are coming from the for each activity. Here we can see either we can select any one of these, but we want to make it as dynamic. So we can click on this edit. So here we can see the option for the dynamic content. But we have to create one of the parameter here and this parameter is the seed name. And this parameter should be going to map with the dynamic content and click on OK. So once we make this change, we can simply go inside the pipeline and now dataset is asking this parameter value, which is the seed name. So again, we can go in the dynamic content. Here we can see the item property. Then we can see the seed name property. So simply we can select the seed name and click on OK. So what will be the seed names will be going to loop through with one by one inside the destination. We want to dump the data inside the Azure SQL. So we can simply go and select the Azure SQL database. Click on continue. We have already created the link service. So we can select that link service. Then we are required to select the table name here. We don't have any table. So let me use the none. Click on OK. And here we are also going to click on this open and table we want to create the table with the name of employee TBL. So by this name, table will be created first. Then all the data should be loading in this table. Let me quickly go on the pipeline level and table option we can select the auto create table. So we have done. Let me try to debug and we will see about the outputs of the activities. So lookup activity got executed successfully. And if we can see it is having count three seed name is the India, US and UK. If we can see in the downside. Now we can see the for each activity is start executing if we can see the output. So item count is three. It means the for each activity will be executing three times. That's why we can see copy data one three times. So if we can simply go inside the SSMS and here let me try to execute this query. So we should be seeing the table which got created right now, which is the employee TBL. So as we can see this table got created. So let me quickly go and try to select the data from this table. So as we can see it is having all the data for the India, UK and US. So 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.