 Hey guys, welcome to SSUnitech, so still decide and this is continuation of SSIS project. So this is part 5 of SSIS project and today we are going to see the one more real time project and this is the complex project. So if you haven't watched last few videos of this video series, so I would strongly recommend to watch those videos. So you can directly go on the browser and here you can search for SSUnitech and after that go inside the channel, then go inside the playlist and here you will see the playlist which is SSIS internal questions and answers. So you can play that playlist and here if you can see I have uploaded few videos which is related to SSIS project. So I would strongly recommend to watch third fourth of this video series. Now go to on the slide. So today as per our project, so we are having a multiple Excel workbook from where we need to load the data into the SQL Server table. But here we have a catch. So on those Excel workbook having multiple Excel seats. So those Excel seats are having either address information or customer data. According to the seat name, we need to load the data on their tables inside the SQL Server. If the seat name is having address, then we need to load the data into the address table. Otherwise we need to load the data into the customer table. So go to the next slide. If you haven't understand about this, so here you can understand. So first we need to use a loop. That loop is going to loop through all the files which is available inside a folder. So it will be going to return the file name along with the file path. After that we need to use another loop. So this loop will be going to loop through all the Excel seats which is available inside the Excel workbook. So here we can get the seat name. And if that seat name contains customer, then we can go inside the customer and load the data into the customer table. If its address, then we can go and load the data into the address table. And inside the address table as we have already discussed in the last video, we need to check inside the country first. If data is not available in the country table, then first we need to load the data into the country table. And after that we need to load the data into the main address table. So don't worry if you don't understand, then so we can go and try to implement this in the practical. So first we can check the source data. So here we are having these two files. So these two are having data for the month end and mid month. So in the real time the data is coming twice in a month and we need to load that data once in a month. So first data which is coming mid month around 15 or 17 and next we are getting the data on 30th. So these two files that we have in this folder, so we need to look through all these two files and loading the data into the table. Go to on the destination. So inside the destination we could see we are having these three tables, customer, address and country table. So we need to load the data in these three tables. Now go to on the SSDT and we will try to implement our package. So now we need to create few variables and those variables will be going to help us while implementing the package. So first thing that variable is required for the source path. So that source path is this one. So in the real time this might be going to change. That's why I'm going to create a variable and this path will be going to use there. And after that we are required to have the second path which is a folder path. So by using the loop that we can say by using the first loop we can get the file name with the file path. So that will be the folder path, the second variable. So it will be going to hold the value on that variable. That is the seed name. So seed name is basically on the second loop we are going to loop through all the seeds. So we are extracting the seed name. So that seed name will be going inside the seed name variable. The last which will be going to check seed name variable. So that check seed name variable means here we are going to check whether the seed name contains customer or address. We need to do certain transformation on the seed name variable to get this check seed name. So nobody will be going to see in the practical. So here inside the SSDT let me try to create these variables first. The first variable that I told you source path. So this is the source path and the data type that should be string and here we can directly copy this path and paste that path in the value side. So it should be like this. Second we need to add one more variable. This variable will be folder path. So this will be folder path and it is a string. The value which is coming on this variable from the loop. The third variable that should be the seed name. So this is from the second loop. And the last which will be the check seed name. So we will be going to do the transformation later on on this. Now here let me save this first. Let me try to drag and drop for each loop container and make it bigger like that. Now double click on this but on the collections. So this for each loop container will be going to loop through all the files in a folder. So we can use the file enumerator right here. Then it is asking the path. So we can browse or we can directly copy that path from here and try to paste right here. But this is the static one. We want to make it as dynamic. So we can go inside the expression and we want to get this excel file path which is directory. And this should become from this source path variable. We can evaluate. Looks good. Click on OK. Again OK. Here we want to extract only the file which will be going to have the excel sx. So excel sx. Whatever the file contains the excel format only those files we want to get. In the retrieve file name we want to get full qualified path. It means it will be going to have the folder path along with the file name. Here we don't have any subfolder. So we don't want to traverse inside the subfolders. Now go to on the variable mapping. So inside the variable mapping we are having this folder path. So this folder path we want to getting from this folder. So what are the files? Those are available. We will be going to loop through one by one and it is going to have the path on this folder path variable. Now we can click on OK. So we have done with the first for each loop container. Let me try to drag and drop the second for each loop container. This second for each loop container we can set here like that. So this time in the collections we are going to loop through with all the excel seeds. So on that case in the last video we have seen we need to use the for each edu.net schema rosette enumerator. Here it is going to ask for the connection. So we can click on the new connection. It will take few seconds. So this is we have already created. Let me delete click on the new. Here it is asking for server name but in the provider we need to select the provider which is OLA DB provider for the access database engine click on OK. Now it is asking for server or file path. So this time I am going to provide path for any of this file. Let me copy this and paste it here. Now we can go in the all and here we are having the extended property. So in the extended property we can call this as excel 8.0. Now we can test the connection. It is succeed click on OK click on OK again OK. So everything looks good. Now we can see inside the schema. So inside the schema we have this table. If we can select this set restrictions then table name is coming in the second index. So 0 for the table catalog 1 for the table schema 2 for the table name. We can click on OK go to the variable mapping. Here we can see the seed name. So in this seed name the index that should be 2 we can click on OK. Now everything is configured for the for each loop container 2 as well. Now what next we need to do we need to go on the variable site. And inside the folder path inside the folder path we can specify a static value for any one of this file. So let me copy any one of this file and go back to here and provide that value right there. It's not required but I am just writing that in the seed name here we need to specify any one of the seed name. So that we could see let me try to copy this address USA first like this. If you have a remember in the last video the seed names are coming with a seed name and dollar. So we can add the dollar right here. Now here we can see this chip seed name. So we can go on the expression and we'll try to evaluate for those. So here let me click on these three dots and here we need to write the expression. So as you can see in the seed name we are having this hyphen. So before the hyphen we want to get the seed name if that is address then data should be go in the address table. If that is customer then data should be go in the customer table. So in this check seed name we are trying to get the seed name before this hyphen. So how we can do that. So this is very simple. We can write the substring function. So this is substring now we can start the bracket. So we want to get this from the seed name. Let me put comma then it is asking for starting value. So we want to start from one. Then how many characters we want. So for that we have a function which is called find string. So we can use that function find string. So first parameter of that find string is the seed name from where we want to find any string. Then what string we want to find that is hyphen. Then third the searching position. So we want to start from first position now we can evaluate it is reflecting an error. Let me check what is that error go back to here. So this is the substring seed name one then the find string. One bracket should be close right here because first bracket for the find string is second for the substring. Now we can evaluate and it looks good. Now we can click on. So everything is set up for the variable side. Now we can close this and here I am going to check all these values. So first of all let me go and try to get the script task inside this for each loop container. Now double click on this and here let me add the variable. So I want to see this check name. I want to see this folder name. I want to see this seed name as well. Now click on OK. Let me click on the edit script. It will take few seconds. Now it's ready. Here we can write the message box to check the output. So let me use the message box dot. So here we can have the DTS dot variables and after that specify the variable like this. So first time I want to see the folder path. Then dot value dot to string and close the bracket. Now second I want to check for seed name so DTS dot variables inside this bracket. We can specify the variable name and this time this is seed name dot value dot to string and we can close the bracket. And last we want to check for the check seed name as well. So DTS dot variables inside the square bracket. We can have the seed name. So it should be like this dot value dot to string like that. Now we can close this and here click on OK. So now everything is ready but I need to make few changes on this. The first change let me go on the edit script again. Here we can see we have here we are checking for the check seed name. We can ignore this because it will be going to take time to return the output in the message box because it is having the calculation. Now we can close this click on OK. Now one more thing go on this connection manager. So as of now it is static connection. So we can go and here go inside the expression and here we are having a property that is server name. So this server name is the file path. So that is coming in the folder path. So we can drag and drop evaluate click on OK again OK. Now we can save this and click on start. So it is checking for the month end. This is for Australia, Canada, others US is for the customer Australia, Canada, others here you can see this is for the mid month for the Australia. So it is working as expected. So let me stop this package. Now here we need to use few things. The first dataflow task we can use and after that we can use one more dataflow task right here. If we connect with the first one and let me call for address and let me call this for customer and here connect with this script task. Now double click on this and here we want to check expression as well as the constant. So inside the expression what we want to do, we want to check for this check sheet name if this value will be have the customer slash. If this is the true then we want to load the data inside this customer dataflow task. Here we want to check for the address. If expression and constant if that value of the sheet name which is the check if this value is address so this is like a double DRE double S address slash evaluate expression so this is true click on OK again. So if we are getting the sheet name which is the customer then that will go in this dataflow task if that is the address so that will go in this dataflow task. Double click on the address first. So inside the address what we need to do so first we need to check for the country table let me connect with the source first so our source should be Excel file so we can drag and drop this Excel file right here then we want to remove the duplicate in the country names so we want district country names so for that we can use the short transformation and after that we want to look up with the table which is the existing table in the SQL server so for that we have to use the lookup transformation and if data is not available in the country table then we want to load the data into SQL server table so for that we can use the already be destination so this is we need to set not only on this Excel source let me click on this new browse this time let me select any one of this file and click on open click on OK here we need to select the sheet name so let me select for the address go to the columns so that is OK click on OK so we have done for this now we can connect this with the short transformation this is static as of now will make it dynamic later double click on this short transformation and in the country name we want distinct values remove W duplicate click on OK let me connect this with the lookup transformation now double click on the lookup transformation full cache is OK here we are going to check inside the SQL server table so already be connection manager is OK go to the connection manager here we need to set up a connection so let me click on new here we need to specify the server name so here we can see the server name so we can copy this and we can go on the ssdt and try to paste it there and after that here we can select the database so we want to load the data into SSIS test database not just the connection it succeed click on OK again OK click on OK here from which table we want to check in the lookup so for that is country table go to columns here we have the country ID and here we can see the country name so let me connect country name with the country name only and in the output that is OK click on OK now let me connect this lookup transformation to the already be destination so here we want no match output but we need to do one more thing so inside the lookup transformation here specify how to handle with the no match entries so obviously redirect rows to no match output click on OK here let me double click on this already be destination on which table we want to load the data so we want to load the data into country table go to the mapping so country name should be going to map with the country name that is OK click on OK again so we have done all these things but everything is static so let me try to make the dynamic in the Excel source first so inside the Excel source we can see the Excel connection manager so here we can go inside the expression and here let me try to use the Excel source path so this Excel source path is coming from the folder path so we can drag and drop evaluate it looks good click on OK again OK so it is OK and now we can double click on this and here instead of this data access mode table or view let me make it as dynamic so we want to get this seed name from the variable and that variable is the seed name now we can go on the columns so it is OK click on OK so everything is OK so we have loaded the data into the country table now we want to load the data into the address table so we can connect this with this data flow task let me double click on this and this time our source that again should be Excel source so we can drag and drop that Excel source here then we want to load the data into the destination so we can drag and drop this destination double click on this source here we can select this address or we can directly make it dynamic instead of the static so this time seed name go to the columns we want all the columns so it is OK click on OK here we can go and connect with the OLEDB destination double click on this here we want to load into the address table go to the mapping so all the mappings are succeed but here you can notice one thing we are having the country ID but we don't have the country ID in the source so how we can get that country ID so for that let me delete this connection first this data pipeline and after that in between we can use the lookup transmission so let me try to drag and drop this lookup transmission connect with the Excel source double click on this lookup transmission everything leave as it is go to the connection here we are going to match with the country table so you can find out the country table put to the columns so we want to match this country name with the country table again and get the country ID from this click on OK now we can connect this with the destination and here obviously for the match output only now double click on this and here it is ok we want to load into the address table go to the mapping so all the mappings are succeed now click on ok so everything is set up for the address side now we can directly jump into the customer side and try to build that as well so double click on this customer and here our source that is the Excel source so we can drag and drop that and after that the destination that should be OLEDB destination so we can drag and drop that as well so in the Excel source let me go in the variable first so as of now it is going to check for the address USA but let me try to make this as customer now because we are going to consider this for the customer now let me close this double click on this Excel source here instead of the static let me make it dynamic so we can select as seed name all the columns are coming so that looks good click on ok here we can connect with this OLEDB destination double click on that so here it is going to load the data into the customer table so we can select the customer go to the mapping so all the mappings should be succeed so it looks good click on ok so we have done for all these if we go so our package is configured successfully before running the package you can go on the package level and here we can see a property that is delay validation because it is going to validate for the Excel seed name and all the things at the starting so that caused the problem because because in the seed name we can provide only one value either for the customer or for the address so one will be evaluated successfully and second will be going to fail so that's why we can enable this delay validation option as true after that we can go on this for each loop container level in all the level we can enable this delay validation so in the address again for true then for this again we can make it as true for the customer again we can make it as true in the connection manager level as we can make it as true for the second and for the last we can make it or not that does not matter so everything is looks good now now in the table first let me truncate all these tables so truncate table so first I am going to truncate this customer table second for the address and last for the country let me try to execute all these so we don't have any data in our table now let me try to execute this so click on the start so this is for the month end click on ok for the australia file so this should go inside the address it should not go anywhere this is because if you can see in this so at the starting we are having the single code so this is the reason this is not going anywhere let me stop this and here while we are going to check let me double click instead of this we can specify single code right there and in the customer as well we can specify single code let me click on ok now let me try to execute this package so before that let me try to truncate all these tables and here we don't have any data now now let me try to execute and load the data into the table click on start so this is for the month end file and this is going to load inside the address so it is going to execute address so first it will load the data into the country table then it is going to load data into this address table second for the canada it is going to do the same thing for the canada and going forward it will be going to repeat all these so this is for the others so this is for the US so now it is going to start with the customer australia then customer canada then customer others then customer us now you can see mid month file is going to start now click on ok so first address australia so this will be going to load in the address table second for the canada so I am going to pause this video until this package is not going to execute successfully so package executed successfully now go to on the sequence over and here we can see the data should be inserted into the table as you can see all the data is available in the table in the real time we are required to delete the data that is truncate the data into the address table as well as in the customer table but we are not required to delete the data into country table so let me double click on this execute and here we can see the connections that we can select and here we can write the truncate statement for address and customer only so country is not going to delete click on ok click on ok so whenever we are going to process the data for these two files then it is going to truncate the data which is available in the customer and address table and at the runtime the data is going to insert in the country table if not available already so this is about this package this is a complex project so please watch this video again if you are not clear and still if you have any doubt then you can drop your questions in the comment box I will try to response on your questions so thank you so much for watching this video please subscribe our channel to get many more videos and don't forget to press the bell icon to get the notification of our newly uploaded videos see you in the next video with new project thank you so much