 Hello, welcome to SSUnitec's society site and this is continuation of SSIS project. So today we are going to see one more real-time project. So here we have a Excel workbook and that workbook contains multiple Excel sheets. So first we need to read the data from all those Excel sheets and loading that data into the SQL Server tables. But here we have a catch like according to the seat names we need to move the data. So directly we are not going to move the data. In the last project we have seen how we can directly read the data from all the Excel sheets and loading that into the SQL Server tables. But here according to the seat name we need to move the data. So go to on the next slide and try to understand about the project in depth. So here like first we need to use the loop that is available in the blue color. So in between like first we need to get the seat name and here we need to check the seat name. So in the source we are having two different types of datasets. First is customer dataset and second is address dataset. So inside the customer dataset if the seat name which is going to start with the customer will be going to on the right side arrow you could see and in case of the address that will be going in the downside arrow. So according to the seat we need to configure that data flow task where we can load the data into SQL Server table for the customer for the customer seat and for the address for the address it. Now go to on the Excel source and have a quick look of the source. So this is our source as you could see we are having the addresses. So address for US, address for Canada, address for Australia, address for other countries. And similarly we have the data for the customer US then customer Australia and customer Canada and customer others. So we need to loop through on all these Excel files and load that data into the SQL Server tables according to the seat name. Now go to on the destination and we will look about the SQL Server tables. So here basically we have three different tables. First table which is country table. Second is the address table and last is the customer table. So in case of the customer table so we can directly load the data from the source to destination because here we have customer ID then title first name, middle name, last name suffix, account number, store ID and address ID. If you go in the source then go to on the customer seat we can see we have the customer ID title first name, middle name, last name suffix, account number, store ID and address ID. So this data can be directly dumped into the SQL Server table. Now go to on the address and country table. So in the address table we have address ID, address line 1, line 2 city, postal code and country ID and this country ID is available on the country table. So we can map with the country ID. So in the source we do not have the country ID if you go in the address. So here we could see we have the address ID line 1, line 2 city, postal code but here we have the country name. So first we need to load the data which is available in the country name into the country table and after that we need to do the lookup on the country name to get the country ID and this country ID is the identity column. So it will be going to incremented one by one as you can also check. So here if you go and you can see the identity, so customer ID is the identity and it is going to start with the one and incremented value that is again one. So this is our requirement and we need to design the package accordingly. So go to on the ssdt and where we'll try to create the package. So this is our blank ssdt file. So first of all as I told you we need to use the for each loop container to loop through all the excel files. So here we need to use this for each loop container. So we can drag and drop it here. Let me try to make it bigger size like this and double click on that. Now go to on the collections and inside the collections we have enumerator. So as we have already seen in the last video, so we can use the for each adio.net schema rosette enumerator. Then it is asking for the connection. So connection you can see we have this file. So we can copy the path and click on the new connection. And here I have already made the connection. So let me delete that and click on new. Here we can see the provider. So.net provider access database or adb provider. If it is not available on your machine then I have explained in the last video you need to download a driver for the same and need to install on your machine. Now click on okay here it is asking for the server or file name. So we can paste that path. Now go to on the all and in the top side inside the advanced we have the extended property. So in the extended property it should be excel 8.0. That you can specify like this. Now we can test the connection. So test connection succeed. So it looks good click on okay again. Okay. Now inside the schema we need to look through on the tables that is seats click on the set restrictions. So we want to get the table name. So table name is coming on the third position. It means 012. So index 2 is containing table name. Now we can go on the variable mapping and here we need to create a variable. So we can click on this and click on new variable and we can call this variable as seat name and click on okay. In the index it should be 2 as you have already seen. Now click on okay. Now we need to check whether this for each look container is going to look through with all the excel seats or not. So for the same we need to use the script task. So we can drag and drop this script task in the for each look container. Let me double click on this script task and here let me select the user seat name variable. Click on the edit script where we need to write the script to check the values whether that is going to look through with all the excel seats or not. So it will take few seconds. Now here we can write the message box dot. So then we can specify dts dot variables and within the bracket we can paste the variable name then the value dot to string. We can save this and we can close that click on now let me try to execute this and we will see whether it is going to look through with all the excel seats or not. So as you can see address Australia, Canada, others US, Australia, Canada, others US. So it looks good. It is going to look through with all the excel file. So here we need to create one more variable and that variable will be going to contain the table name table name means here inside the excel file we have the table name that is address then we have dash US then address dash Canada. So before this dash that is a table name. So on that particular variable we need to calculate this now go to on the ssgt right click on this go to on the variables and let me click on this new variable and let me call this as seat name new data type that should be string and inside the expression we need to write that. So here let me close this let me provide a dummy name for this like customer dash US dollar. So this will be the seat name. Now let me click on the expression and we need to evaluate. So first we have the seat name we can evaluate and it is having customer dash USA dollar. So here first I am going to use the find string. So find string is a fulcrum that is going to use to find any particular character within a text. So first that is from where we want to search out. Second what character we want to search and third variable from where we want to start. Let me click on evaluate expression. So it is saying it is having an error while trying to convert this to the string. So let me copy this cancel this try to change this to integer 32 go back to here paste that and try to evaluate. So it is going to return nine. So we can start counting like one two three four five six seven eight and nine. So on the ninth position we have that but what we are going to do first let me try to change this to string and go back to here. First we need to use the substring function. So inside the substring function we want to get the text from the seat name from where it is going to start that is one and second will be this find string. Now we can evaluate. So it is going to return customer dash that is okay. We can click on okay and we can save that. So on the basis of that particular variable we need to go on the address or customer table. So we need to drag and drop two dataflow tasks one for the customer as second for the address. So let me configure for the customer first. So here we have the customer we can connect from this script task. Let me double click on this and in the evaluation operation we need to use the expression and constant both while we have this success on this script task and in the expression we have this seat name new that should be equal equals to if it is going to have the customer dash then we want to load the data from this dataflow task. Here it is very straightforward like in the source that we have the excel source so we can drag and drop and in the destination that is already be destination. So let me try to configure this excel source and already be destination. So double click on this excel source click on new we can browse for the source and here the file so we can click on open click on okay. Here we can see the seat names but our seat name is coming from this seat name variable. Now go to on the columns so everything looks good click on okay. We can connect double click on this click on new connection so we have already made this connection so let me utilize that and on which table we want to load the data so we want to load the data on the customer table. So here we have this customer table now we can check the mapping so mapping succeed click on okay. So here we have done for the customer side now we need to check for the address side. So on the address side this would be address as I told you first we need to load the data on the country table. So this dataflow task will be going to load the data into country table. Let me double click on this so before going to configure this dataflow task let me double click on this precedence constant and again in the evaluation operation it should be expression and constant. We can click on this three dots and go to on the seat name new. This time we are going to check if we will be going to see address dash then we want to load the data so it looks good click on okay everything is fine now let me try to configure this address country table. So again our source is excel source and our destination that should be already be destination but in between we need to validate whether data is already or label or not. If data is not or label in the destination table only then we want to load the data. So for that we need to use the lookup transformation. So let me configure the excel source first double click on this so it is going to use the same excel file here we can see we are having the seat name which is address. So this time let me try to make the change on the variable side. So go to on the variable and this time I am going to put default value as address. Now double click on this address double click on this excel source and we have the table name in the variable and variable name is the seat name. Go to on the columns so we have all these columns that is okay but here we don't want to get all these columns we are very excited to have only the country name. So here we will be going to get the duplicate country names but we don't want to see the duplicate country names so for that we can use the short transformation. So we can drag and drop the short transformation to remove the duplicate from the excel source double click on this we can select that and here we can see remove rows with the duplicate short value click on okay. Let me connect this with the lookup transformation not double click on this lookup transformation. So here we have the cache option so full cache is okay and our connection type is the OLEDB connection that is also okay go to on the connection. Now here from which table we want to do the lookup that is our country table and on which column that is the country name column. So it looks good everything is okay now go to on the general so here we can see the specify how to handle rows with the no match entries so here we can ignore the failure if we will not have any entries in the no match output not click on okay here we can connect only the no match output data. So if data is already available in the distribution then we can ignore those if data is not available then we want to load that data into the table. So let me double click on this OLEDB destination and here let me select the country go to on the mappings so country name is going to map with the country name that is okay and we can ignore for the country ID because this is the identity column so it will be going to insert click on okay. So we have done with the countryside so once data is available in the country table then we want to load the data into the address table so we can drag and drop and we can connect here let me try to rename this for the address main table not double click on this so here the first thing that we need to drag and drop for the source so our source is the excel source so we can drag and drop second we need to get the country ID so by using lookup transformation we can get that and at last we want to load the data into OLEDB destination site so everything looks good let me double click on this excel source and here we have the seek name in the excel in the variable so we can choose the second option in the data access mode and that is seek name go to on the columns so we are required to have all these columns so click on okay let me connect this to the lookup transformation double click on this and go to the connection so we are okay with that connection in the table site we can select the country table in the columns we are going to match with the country name with the country name and from this we are required to have the country ID click on okay and here while we are having the match output so we need to go in the destination site here we can ignore the no match output so we can ignore on that scenario so this scenario is not going to happen anyhow because while we are going to insert the data those are our label in the excel seed first and after that we are going to use the same data for the lookup so in the no match output will not have anything so double click on this destination and here we want to load the data into address table go to on the mapping site so mapping got succeed so everything looks good click on okay so we have done with all these so once we are going to execute this package so it should be going to reflect an error let's try to execute and we'll see so once I click on start so it is saying it is reflecting an error because if you go in the customer default value we have set for the address so that's why we are going to reflect an error so here we need to do the delay validation option will be going to true so that is going to check at the runtime while it is going to compile it should not be going to check with all the connections and validation so in the excel connection manager we could see for the delay validation option so we can click on true that is okay now go to on the package level in the package level we can also set it as true so delay validation option that should be true now let me try to execute this so once I try to execute it has a starting execution so first is the address Australia so second is the address Canada then address others address us like that so here you need to check we are having this single code before this name so that's the reason that's why we are not going to see anything on the data flow task so let me try to stop this package and here double click on this precedence constant and here we need to go back to here and specify one single code click on okay similarly we need to do for the address as well now click on okay now we can start execution so first for the address Australia so it has a start execution so it has inserted the data into the country table then it is start inserting the data into the main table second for the canada again it is going to do the same thing first inserting the data into the country table second in the main table next for the others now for us now it is going to start with the customer Australia so it got filled so we need to check why it got filled so we can go on the downside so it is reflecting the same error that I was talking about so go back to here on the customer side so here we can see the delay validation option let me try to prove that we can save this and now in the table side we should have the data into the address tables but in the country table we don't see any data so go back to the package and we'll try to evaluate so first let me try to use execute SQL task to truncate all these three tables and let me double click on this and here let me add the same connection and in this statement let me try to write the statement so truncate table and first it should be for the address second for the country and last for the customer copy that go back to the package and here in the SQL statement paste that click on okay again okay so as we have seen we did not have the data in the country table so go to on this address country and here go to on the lookup transmission so here like I have made this mistake so specify how to handle rows with the no match entries so here we want to move the data which is no match entries in the no match output so now it will be going to load the data in the country table and as we seen this data flow task for the customer got filled because here we need to use the delay validation is true for this and delay validation should be true for this for each loop container as well and on this level also we need to check this as true so the delay validation that we need to set on the package level on the task level and on the data flow level so that we need to set out so I have set all these so let me try to execute this package and we will see what will be going to happen so first it is going to load the data for the Australia so it is inside the address country table then inside the address main for the Canada for the others for the US now this will be for the customer for the Canada for the others and last for the US so all these data should be loaded in the tables now package executed successfully let me stop this package and go to on the SSMS and here let me try to execute this selected statement so in the address table that we could see we are having all this and in the country table we are having all the countries and in the customer table we are also having all the customer entries so I hope guys you have understand how you can design this package and if you still have any doubt then you can drop your questions on the comment box I will try to response on your questions so thank you so much for watching this video if you really like this video please subscribe our channel to get many more videos don't forget to press the bell I can to get the notification of our newly uploaded videos see you in the next video