 Hey guys, welcome to SS intact social decide and this is continuation of a science project. So in this video, we are going to see how we can read the data from the Excel seats in a single workbook. And we are also going to see how we can use the for each adio.net schema rosette numerator. So what is our plan? Our plan is first we are going to read the data from the Excel seats and then we are going to compare that data inside the SQL Server table. If in the Excel file, we are getting the updated data, then we need to update that data into the SQL Server table. So let's have a quick look of the source where we can see about the data. So in this Excel file, as you could see, we are having seat one seat two seat three seat four and seat five. So first, we need to read the data from all these Excel seat. And second, here we are having the data for the product ID name safety stock level standard cost list price and subcategory name. So first we need to check about the standard cost and list price. If it is updated inside the Excel file, then we need to update that inside the SQL Server table. And this update could be going to happen with the product ID and subcategory ID. Here we are having subcategory name. So by using the subcategory table, we need to get the subcategory ID. So that we'll do later in this video. So go to on the SQL Server and let's quick look off the table. So here I'm using AdventureWorks 2K17. So if you don't have that database, you could install on your system. So here as you could see, I have written the select statement. So let me try to execute this. So I have executed only for these two product IDs. So here as you could see, we are having this product ID, product name and other more information are available. But we are more concentrate on this standard cost and list price. If anything is going to change in the Excel file, then that should be going to updated right here. So here I have created the basic overflow how we can design the practice. So as you could see, first we are having this for each loop container. So this container will be going to loop through all the Excel seats. Once we are getting the seat, then we should get the data from the source, like from the seat 1, seat 2, seat 3 and seat 4. Then by using the lookup transformation, we need to get the subcategory ID. If data is matched, then we should have two outputs. First for the match output in this lookup transformation. Second we have no match output. If we get the no match output, then on that scenario, the source data that we are getting should be loaded in the error file. If that is matching, then by using OLEDB command transformation, we need to update the list price and standard cost. So that is our plan in this video. So go to on the SSDT and we will try to design the package. So here this is our sequence over data tool. So first we need to use the for each loop container. So here we can directly drag and drop this for each loop container. Let me try to make it a little bit bigger size. Let me double click on this. So once we double click on this, here go to on the collections. So inside the collections, we could see the option for the enumerator. So here we can see this for each ADO.NET schema rosette enumerator. This enumerator is going to use for loop through all the Excel seeds in a single workbook. Now go to on the corrections. Let me try to create a ADO.NET connection. So click on new connection. Here I have already created the correction. Let me delete this and click on new connection. So it will open an window. So here we could see the provider. So we can open that here we could see the provider for OLEDB. We can expand that. Here I am able to see this Microsoft Office 12.0 access database engine OLEDB provider. If you are not able to see this provider, then you need to go on the Google and you can search for this and try to download. I'll provide this link on the description of this video. So you can directly go and download if that option is not available for you. So let me select that and click on OK. We need to specify server or file name. So our file is available in this folder. So we can directly try to copy this Excel file and go to on the SSDT and paste it right here. Now we can go on the all. In this all property in the top side we can see the extended property. So inside the extended property, you need to write the Excel 8.0. Now we can test the connection. Connection succeed. We can click on OK, click on OK, again OK. Inside the schema, we are more concentrated on the table. So inside the table we need to get the seat name only. So inside the restrictions we could see seat names are coming third point which is the table name. We need to remember this point like 0, 1, 2, 3. So this is the index. So table names are coming in the index 2. Now we can click on OK, go to on the variable mapping. So here we need to create a variable. That variable will be going to hold the seat name. So we can directly click on new variable and this name should be seat name and everything is OK. So inside the index it should be 2 because we have already seen table name were coming in third position. Now click on OK. So we are done with this for each loop container. Now we need to test whether this is working fine or not. So for that we need to use this script task. So we can drag and drop this script task. Now double click on this script task. Here we need to take that variable that is created. So that is seat name, click on OK. Let me copy this and click on this edit script. So it will take few seconds to open the script editor window. So here we could see this main block. So here we need to write the masses box dot so and in between we need to write DTS dot variables and here we need to specify which variable we want to print. So we want to print this seat name variable. Then dot value dot 2 is 3 close the parenthesis. Now everything is OK. We can save this and we can close it click on OK. Now we can execute and test our package. So it should be going to loop through with seat 1, 2, 3, 4 and 5. So this is seat 1, seat 2, seat 3, seat 4, seat 5. So everything looks good. We can stop this package and now we need to use the dataflow task now. So dataflow task we can drag and drop in between this for each loop container below of this script task. We can connect with the script task. Let me double click on this. So here we need to design the package. So first what is our source? Our source is Excel file. So we can drag and drop this Excel source right here. Double click on this Excel source. Now we need to make the connection. So click on the new. It will ask for Excel file path. We can browse and select that file. Click on open. Click on OK. So here it is asking for name of the Excel seat. So here we should not apply this name as it is because the name are coming from the variable. So we can select that variable. Go to on the columns. So it is not going to work. So first time let me try to use this seat name. Then inside the columns everything is OK. Click on OK. We will make that change later. As of now leave as it is. Go to on the lookup transformation. So we can drag and drop this lookup transformation. Let me connect with the source. Double click on this. Go to on the connections. So inside the connections we need to create a new connection. Click on new. Here we can specify the server name. So click on this drop down. Now we can select this and inside the database we need to select adventure works 2k 17. Click on OK. Again OK. Inside the tables so we need to select the product category table. So that should be available right here somewhere. So this is product subcategory. Go to on the columns. So here we are having this product subcategory column and in this table we are having this name. So this name should be going to match with the product subcategory name. Here we want to get this product subcategory ID. Now click on OK. So this lookup transformation will have two outputs first for the match output and second for the no match output. So for the no match output we need to load that data into the flat file. So we can drag and drop this flat file destination and connect this with the lookup transformation and this should be for the no match output. Click on OK. Let me create a connection for this flat file. Click on new. Click on OK. Click on browse. We can select this Excel data dot txt or you can create a new one. Click on open. That should be the column names. Everything is OK. Click on OK. Go to on the mapping. Mapping succeed. Click on OK. So we have done for the no match output. Next we need to check for the match output. So for that as I told you we need to use the OLA DB command transformation. So we can drag and drop and we can connect. So this should be for the match output double click on that. Here we need to select the connection manager. So this is connection manager that we have already created. We can select go to on the component property. Inside the component property we should see for the SQL command. So here we need to write the update statement. So I have already written that statement. So go to on the SSMS. So this is the update statement. So we are going to update this product table where the list price is question mark standard cost question mark product ID question mark and product subcategory ID question mark. So let me try to copy this and go to on the SSGT and paste that query. So first for the list price second standard cost product ID product subcategory ID. You need to remember about this sequence list price standard cost product ID subcategory ID click on ok go to on the column mapping. So here first if you have remember that is the list price. So we can drag and drop for parameter 0 second is the standard cost. So we can drag and drop with the parameter 1 third is for the product ID. So we can drag and drop for product ID and fourth is for product subcategory ID. So we can drag and drop everything looks good we can click on ok. So we have done with that let me double click on this excel source and here let me use the variable and click on ok. So it is reflecting an error. So first go to on the variable side and inside the variable let me put a default value on this that should be seat $1. Now we can save this double click on that make the change and click on ok. Here we are getting this warning because it is saying no rows will be sent to the no match output. So that is ok we can ignore that. Now if you are going to execute this package so data that should be going to update in the table. So first go to on the seat. So in the seat one we are having ID 680 and 706. So we have already selected for these two. Let me go on the other one like for the seat 5. So here we are having these two 939 and 940 let me include these two as well. So we can execute and go to on the excel file. So as of now we could see we are having the standard cost which is something 27. So let me add one right here. Second let me add one right here. One right here and one right here. So it is updated in the excel file now for the seat 5. In the seat 1 here we could see standard cost 1 so it should be 2. We can make the change for this. It should be 2. We can also make the change in the list price. We can remove one from here and we can remove one from here. We can save this if we are going to execute the package. So this data should be updated on this seat 4 table. So here as of now we can see we are having this 10. Let me try to copy this and go to on the new window and try to execute this. Right click on this, split right here. So if you want to compare with the standard cost and list price then we can easily compare. Now go to on the ssdt and try to execute the package. So this is for seat 1, seat 2, seat 3, seat 4, seat 5. So package is executed successfully. We can stop this package, go to on the ssms and try to execute this. Now we can check whether the values are updated or not. So here we could see values perfectly updated as per our expectation. So everything is updated and this is done now. So here we need to more focus on one thing because here we are going to use this OLEDB command transformation. So OLEDB command transformation is very slow because it is going to check row by row. So how we can do the performance tuning on this SSIS package. So that we can also do. Let me try to create a new package which is the copy of the existing one. So we can go and paste that and let me try to rename this. Just with the 2 and we can leave that. So this is the second one. Let me close the first one. So what changes are required? So we do not want to use this OLEDB command transformation. So for that we need to load all the data in a single table and after that we can update the data from that table. So how we can do that? So we need to create an intermediate table or the staging table. So go to on the SSMS and here let me switch back to the filter and here we need to execute this create statement. So basically we are going to create this production temporary table and it is containing product ID, what is the product name, safety stock level, standard cost, list price and subcategory ID. So only these columns this table will have. Let me try to execute this. So this table is already exist in the database. So I am going to use as it is. Now go to on the SSDT and here first we need to use a execute SQL task before going to execute this for each loop container. So we can drag and drop it here and we can connect. Let me directly on this here it will ask for the connection manager we have already created inside the query. This is very straightforward like we want to truncate table and what is the table name we can directly copy from here and paste right there. Now click on OK. Now we can close this. So we have configured this. So this execute SQL task is truncating temporary table. Now here double click on this dataflow task. Let me delete this OLEDB command transformation. So instead of OLEDB command transformation we need to drag and drop this OLEDB destination. Now we need to connect with the looker transformation and double click on this. Here everything is set up. We need to only select this production temporary table go to on the mapping. So mapping should be succeed now click on OK. So all the data should be available on this OLEDB destination. So after loading all the data from all the seeds from this temporary table or this staging table we need to use one more execute SQL task and that will be going to update in the main table. So we can connect after execution this for each loop container double click on that. Here we need to select the connection manager that we have already created. Here in the update statement I have already written that. So that is very straightforward like we are going to update this P table which is the product table from this production temporary table and we are joining on the basis of product ID and product subcategory ID and updating list price and standard cost. So let me copy this update statement and go to on the SSDT and try to paste it here. Click on OK again. So everything is OK. Let me rename this. So this is updating product table from temp table. Now if we are going to execute this your tables data should be updated. So here let me close this vertical tab. So if we execute this. So as of now what we could see the data that is updated here go to on the Excel file and try to make the changes it should be 59 only and this should be 59 only and here we could see 31 only here we could see 31 only. So this is from the seat 1 go to on the seat 5 and inside the seat 5 we can have only 17 and here we could have only 15 and in the list price we should have only 12 and we should have only 10.99. Now go to on the SSDT and try to execute the package. So once we execute the package the data that should be updated. So this is for seat 1, seat 2, seat 3, seat 4, seat 5 and packages is executed successfully. Now we can stop this package and go to on the SSMS and here let me try to execute and try to match the list price and standard cost. So here list price was 2059 so here we have updated only 59 and here it is only 31. Here we have 17, 15 so everything is updated as per our expectation. I tried to explain this in the simplest way. So thank you so much for watching this video if you still have any doubt then you can drop your questions your queries in the comment section and if you stuck anywhere in the project then you can also share that in the comment section. I will try to record a new video for your questions. Thank you so much. See you in the next video with the new project.