 Hey guys, welcome to SSUriTeX.com and this is continuation of SSIS project. So this is part 7 of SSIS project. So before going forward, if you haven't watched the before uploaded projects, so I strongly recommend to watch all those videos. So for that you can go on the YouTube and after that you can search for SSUriTeX. Then you can go inside the channel. After that you can go in the playlist. So inside the playlist, you will see this playlist for the SSIS project. So you can play and here I have uploaded total 6 projects. So you can watch all these videos and do the practice on your own. Now go to on the today's project. So in this project, we are going to load the data from excel file to the SQL server tables and source will have multiple excel files. So we need to load the data according to the file names. So if your file name contains the date which will be Monday, Wednesday or Friday, then data should be loaded into the customer table. If that will contain Tuesday, Thursday or Saturday, then data should be uploaded into the address table. So what does it mean? Go to on the source and we will check about the file name. So here go to the source. So as you could see the source, so your source file contains the timestamp along with your file name. So that you could see like 2021, 1025, then 2020, 1026. So 15 as you could see, 15 will be having Monday, 26 will be going to have Tuesday, then 27 will be having Wednesday. So 25 should be contain the information of the customer table and 26 will be having the information for the address. So accordingly, we need to load the data into the SQL server table. And inside the destination, we are having these two tables where data should be loaded. So this is our requirement based on the file name where we are having the timestamp, data should be loaded into the tables. So how we can do that? So for that here inside the SSD team, we need to loop through all the Excel files, those are available inside that folder. So here we can drag and drop for each loop container. And it should be going to use as file enumerator. So we can double click on this. Here go to on the corrections and inside the correction inside the enumerator, we should be going to use file enumerator. Next, we need to specify the folder path from where we are going to loop through. So this is our folder path, we can copy this and we can paste it here like this. After that, we want to get all the files, those are available inside this folder. After that, we want to have this fully qualified path. So it will be having your folder path with the file name and extension. So this option we are going to use. Next go to on the variable mapping. So we want to have a variable that would be your file path. So we can add a new variable and let me call this as file path and the data type that should be string. Let me click on ok, index 0 should be ok and we can click on ok. So we have configured this for each loop container. So now we are having a variable and that variable will have the folder path with the file name. So let me copy any default value for this and go back to the SSDT and paste it here. Now we should be going to extract this timestamp from this file path. So let me add a new variable and let me call this as file name, data type that should be string. Here we can go on this expression. So we need to write the expression. So first let me drag and drop this file path. Now first we want to reverse this in the order. So it will be going to reverse this string. So let me use the reverse function and evaluate it. So everything is reversed. Now we want to get the values before this underscore. So everything which is available before this. So how we can do that? So for this we need to use the left function. We want to get it from this string that we have reversed. Then we need to use the find string. So this function will be going to check about any character and will return the location of that character. So again we need to use the reverse function. Then we need to use this file path put comma. What we want to check? We want to check this underscore. Then put comma one. Then we can close this bracket. Let me try to evaluate this. So it is having an error. Let me recheck about this. So left function reverse is completed. So one bracket should be closed here. Now we can evaluate. So everything looks good. One thing we don't want this underscore. So we can use minus one right here. Now next we want to replace this with a blank which is xslx. So that we want to replace. So let me use this replace function. So replace from this string what we want to replace so that we have xslx. with a blank. So let me evaluate. So everything looks good. Now we want to reverse this. So reverse this in the order to evaluate it. So we have extracted the timestamp part only from that folder path. So that part is done. Let me click on Next we will be going to use the execute SQL task. So what execute SQL task does? It will take this input of this variable and will be going to return the week of day. So what does it mean? For example, it will be having 2021, 1025. So it will take as an input parameter and in the output it will be going to return as Monday. So this we need to create a store procedure and that store procedure will be taking this input parameter and will return the output. So go to all the SQL server and here let me try to write the store procedure. So create proc sp get date as an input we are going to take date and this is wire care 20 as begin and. So first let me declare a variable this variable with the wire care 20. In this variable we need to set at the rate dt. So what we are trying to do? We are trying to make this input as proper date. So as you could see first 4 is here then next 2 will be month and last 2 will be date. So here let me going to have the year first. So left from this date put comma 4. So this is the year plus dash then plus let me use the substring substring function from this date. Now it is starting position will be fifth because first 4 is the year and next 2 character we want. So this is for month then dash and last for the date. So we can use the right function right from this date and 2. So this at the rate dt will be having a proper date. Now we can use the select and let me get the date name and we want day of week as an interval. So we can use week date. Let me put comma from this dt and let me call this as date. Now execute this stropo seizure and let me check this. So we can execute as an input let me have 2020 1 0 2 5. So this will return Monday. So as you could see it is returning Monday. So everything looks good. Let me copy this go back to the ssdt go to on the execute SQL task. Here let me create a connection with any one of the database. So here we have already made the connection with the destination. So I am going to use the same. Now in the SQL statement we can write the execute of this with the question mark. Let me click on okay. Now go to on the parameter mapping. So inside the parameter mapping we will be going to add a parameter. So this parameter should be file name, data type that should be worker and this should be zero. Go to here and this sp will be going to return one result set. So instead of the none it should be single row. Go to on the result set click on add and here let me create a new variable. So this variable will hold the day. So let me call this as day click on okay. This should be zero. So what it mean? So this is your input parameter of the stropo seizure. So that will be going to take this date and in the output parameter this Monday. So this Monday will be in the result set of this day. Let me click on okay. So we have done with this execute SQL task. So this execute SQL task will return day of week. Now we need to use two dataflow tasks. So first dataflow task for the customer and second dataflow task should be for address. So let me rename this as customer and this should be address. Let me connect with this execute SQL task and with this execute SQL task. So here we need to check in this precedence constant with the expression and constant. So for the customer as you could see. So customer if Monday, Wednesday and Friday. So let me add this expression for this day. If this day value is equals to Monday or this day value is equals to Wednesday or this day value is Friday. It is returning an error due to double equals to A. Now it looks good click on okay. Again okay. So this precedence constant is checking if your execute SQL task will be returning either Monday, Wednesday or Friday. So this dataflow task of this customer will be executing. Let me check for the others. So instead of constant it should be expression and constant go to here. Let me drag and drop this day. This should be equals to Tuesday, Thursday. This day is equals to Saturday. Now we can evaluate and check. So it looks good click on okay. Again okay. So we have done with the precedence constant. Now let me configure about this dataflow task. So let me double click on this customer. Here our source is Excel file. So we can drag and drop this Excel source and we need to load the data into the SQL server table. So early DB destination will be the destination. Let me double click on this Excel source, click on new, browse in the source, select the first one because Monday is the customer and this is Monday. Click on open, click on okay. Here we can select this go to the columns click on okay. Let me connect this with the early DB destination. Double click on this and we need to load the data into customer table. Go to the mapping, click on okay. So everything looks good. Now let me configure about this address. So this Excel connects the manager for the customer. So let me rename this first with the customer. Now again we need to get the data from the Excel source and loading that into the SQL server destination. So double click on this Excel source, click on new, browse. This time this should be address. So I have selected for Tuesday. Click on open, click on okay. Need to select this seat, go to the columns, click on okay. Connect this Excel source with the destination. Double click on this, select this address. So mapping got succeed, click on okay. So done with the mapping as well. So here as you could see we have configured everything but this is for static. So every time it will be going to refer the same Excel file and load the data from that file. So we need to make the dynamic as per the files look container. So go to on the connection manager and go to the properties and go to the expression. So here we have a property which is Excel file path. So this Excel file path should come from this file path and click on okay, again okay. Now go to on the address side here again for Excel file path. This Excel file path should come from this file path variable. Click on okay, again okay. So here we should have a property that is delay validation. So this should be true for these two connection managers. So everything looks good but here as you could see it is having an error. So we can right click, go to the variable and instead of this 25 let me make this as 26. Now double click on this, double click on that. So looks good, click on okay. So everything looks good. So now we can execute these packets and data should be loaded into these two tables which is customer and address table. As of now we do not have any data in this. Let me try to execute it. So once we have started execution. So it is reflecting an error due to this customer click on okay. Here we are having the problem. So let me go on this package level and try to on this delay validation of some. Let me try to execute it. So package is executed first time, second time, third time. So it got executed successfully. Stop this package, go to here. Let me try to execute it. Data is inserted successfully on these tables. So I hope you have understand how you can design this project. If you have still any doubt then you can drop your questions in the comment box. I will try to response over there. And if you have any scenario or any project then you guys share with me or drop in the comment box. So I will try to record the video on that scenario and project. 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 icon to get the notification of our newly uploaded videos. See you in the next video with new project.