 Hello, Welcome to SSINI text who will decide and this is continuation of Azure Data Factory interview question answers. So, before going forward, if you haven't watched the last video of this video series, I would strongly recommend to watch that video. So, this is the continuation of that video. So, here the scenario is how we can copy the data from multiple Excel seats to multiple tables. And in the last video, we have seen how we can copy multiple Excel seats data into a single table. What is the requirement? Let me quickly go and show you the Excel file. So, here is the Excel file. So, as you can see, we are having multiple seats and we are having like Employee India, Employee US, UK, Department India, then Department UK, US. So, all these data is here. So, as per the requirement, we need to keep these data into two different tables. Employee data will go into Employee table and Department data should go in Department table. So, as we can see, if your seat name contains something EMP, then that is the Employee data. And if your seat name contains like DEPT, so that is for the department, you can differentiate between these two seats. So, we have already placed this file in the Azure Blob Stories. So, we don't have any option inside the Azure Data Factory. By that option, we can loop through with all these Excel seats. So, we have to create a config table or the config file by which we will be keeping all these Excel seat names. So, in the last video, I have used about the CSV file as a config. In this video, I am going to use as a SQL table. So, let me quickly go and try to create the config table. So, that is the config table I am creating. And I am also inserting the data into this config table. So, as we have already inserted the data for all those seats, by next time, if you are receiving the file, those may be having more than these Excel seats, then we can simply insert the data into this config table. So, it will automatically pick inside the Azure Data Factory once that will be executing. Now, we are having two other tables. One is the Employee Table. Second is the Department Table. So, let me try to create these two tables as well. So, once we will be inserting the data for the Employee Table, we will go in this table. And for the Department, that will go in this table. Let me quickly go inside the browser and we will try to create the pipeline. So, here as we are inside this input container and we can see the source data file. So, this is the source file. Let me quickly go here and try to add a new pipeline. So, here we have to use couple of activities. So, first you should be having good idea on the lookup activity, then for each activity, if condition activity and copy data activity. So, if you don't have the idea for these four activities, so I will provide the link of these videos in the description of this video. You can watch from there. So, let me use the lookup activity first. So, what lookup activity will do? It will be fetching all the data that is available into the conflict table. So, let me quickly go into setting and here let me add the source data set. And your source data set is the SQL table. So, we can select this as your SQL database. We have already created this link service. So, I am going to use that link service. And inside the table, if we can scroll then we can see the conflict table. So, that table should be here. Here we can search for the conflict table. We can select that table and click on OK. So, once we are creating this data set, then we can see the option for the first row only. So, what it will be returning? It will return the first row from your table. But we want entire output. So, we can unselect this. Let me try to execute it and we will see the output of this lookup activity. Now, we can check the output. So, as we can see the count is 6. So, if we can see here, we are having this seat name as a column. So, that is why we are seeing the seat name there. And all these 6 values we can check. Like the employee India, UK, US, then department, all these data. Now, we need to use the for each activity. So, what for each activity we will be doing? It will be going to look through with all these Excel seats that we are fetching the output of this lookup activity. So, if we can go here again, then I can show you this is one of the array. An array value is containing for all these seat name. So, in the for each activity inside the settings inside the items we should be seeing the lookup array. So, this array value we should be selecting as the input of the for each activity. So, it will be looping 6 times. Now, inside that we can go in the activities and under the activity we should be using the if condition activity. So, inside the if condition activity, we are checking the seat name. If your seat name contains EMP, then that data should go into EMP table. Otherwise, that will go into department. So, inside the activities, here we can see the expression. So, here we need to check the name from the for each activity and the property that is the seat name property. If this value contains, so here we can go inside the functions and we can search for the contents. So, if this seat name value that we are receiving contains that is EMP. If this value is true, then inside the true we should be going to load the data into the employ table. Now, we can click on this pencil icon of the true and inside this true simply we can use the copy data activity. So, this copy data activity as our destination is static but your source is dynamic because the seat name that we are receiving from the for each activity. So, here we can see the source dataset. So, here let me click on new and try to create a new dataset and this is for the Azure Blob Stories. So, we can select because your file is available on the Blob Stories and the format is the Excel. Click on continue. Here, we can select this link service that we had created earlier. We can browse for the path, go to input and simply we can select the file and click on OK. After that, we can see the seat name. So, as of now I am going to select the index and here I am specifying 0. So, we will make the change later, don't worry for now. Here the first row as header we can select this checkbox and click on OK. So, it will be creating this dataset that is for Excel 1. Now, after creating this we can click on this open. So, here we will try to make it as dynamic. Simply, we can create a parameter here and this parameter is for the seat name and in this connection we can see this seat index. So, I am going to go with the seat name and here we can see AddDynamicContent. We can use this parameter. And this parameter value we should be getting from the for each activity. So, now this dataset is asking this input parameter. So, we can go into AddDynamicContent. Here, we should not see about the for each activity. But we can use that. So, we can use the item and after that we can use dot seat name. Now, click on OK. So, we have done with the source. Similarly, we can go inside the sync and here we can create a new dataset and it should be going to load inside the Azure SQL database. Click on continue. Here, we can select this link service that we had created and in the table we should be selecting the employee table. So, we can see employee TBL here. So, we can select it and we can click on OK. So, if your if condition will be true then data should be loading in this table. If that is not true then on that scenario we can see this false. So, under the false again we have to use the copy data activity. And this copy data activity your source we can use the same as we have created that is for the Excel one. Because your seat name will be dynamic and your seat name should be getting from the item dot seat name. Now, click on OK. So, we have done with the source. Inside the sync we need to make the change. So, let me create a new one and Azure SQL database link service we can select and this should be going to load in the department table. So, we can select the department TBL here. Now, click on OK. Let me try to execute and we will verify. So, let me try to click on this debug. So, it is executing now. In between, let me quickly go inside the SSMS and here let me try to use the select statement. So, select a stick from your employee TBL, the first one and second for the department one. As we can see, data is start loading. So, your pipeline is executing. So, if we can refresh and we will see some of the copy data activity should be completed with success I guess. So, all these got executed successfully. So, let me quickly go here and we will see the output. So, let me execute it. And here we can see it is having 20 one row and similarly we can see it is having total 7 rows. So, data is inserted successfully on these two tables. So, I hope guys you have understand how we can solve this problem. If it is not clear then you can watch this video again. Thank you so much for watching this video. See you in the next video.