 Hello, welcome to SSGTEC, social this side and today I am going to discuss SSIS project part 1. So first of all, I am very thankful to you all for watching and appreciating me for SSIS videos. And today I am going to record this video as per your request, as like you are not working in the SSIS but you have learned about the SSIS. In interview, interviewer wants to know about your current project. So you can explain this project with the interviewer. So let's start with the project. So before going to watch this video, I am strongly recommend to watch SSIS tutorial part 3 for loading data flat file to database, then 10.1 for dynamic connection manager, then part 50 and 51 for file system task. After that part 65 for execute process task. So these videos will help you to understand about this project very easily because I am going to use these functionality with this project. So move to next slide to understand about the project. So here, what is our requirement? So in this project, we are getting the flat file on daily basis in a specified folder. So first we just want to load data from flat file to SQL Server table. Once data will be loaded, then I just want to rename that file like we have test.xt file. So that file should be renamed with the year, month and day like this. Next, I just want to move this file into the archive folder because data is loaded into the SQL Server table. Next, I just want to zip that renamed file which is renamed over here and moved into the archive folder. After that, I just want to delete that move file. So this is the process which we want to do. Let's have a look of the flat file. So first of all, here as you can see we have this flat file. So we are getting this flat file on daily basis in this folder. We just want to create a package which is going to load this data into SQL Server table. After that, this file should move to archive folder. So this is the archive folder. After loading data from this file, it will move to archive folder. Then I just want to rename this file with the year, month and day and we just want to zip that file inside the archive folder. So this is the process we want to do and this data will be loaded into SQL Server table. And here let's have a look of SQL Server table. So as we can see we have this table and currently we are having 348 records. So this is the project and you can explain with the interviewer like we are getting this file into daily basis and our package will load data from this file to SQL Server table. After that, move this file to archive folder and inside this archive folder, we will rename this file and zip that file. So this is the project and let's move to SSDT to design the project. So here we just want to get data from flat file and then load that data into SQL Server table. So we have to use dataflow task for that. Let me use this dataflow task. Then I click on this dataflow task. Now our source that is flat file. So we can drag and drop source. Next we want to load this data into SQL Server table. So that should be already be destination. So we can drag and drop this already be destination. Now double click on this flat file. Then click on new and here we need to select the file. So we can browse. Now we can select this file and click on open. Now go to one columns. So we are having all these columns. We are happy with it. Now click on columns. Here we want all the columns. So that is good. Click on OK. So we have configured our source. Now let me configure our destination. So double click on this destination and here let me connect with the connection manager. So this is the connection manager I have already made. So let me use that one. Click on OK. Now we need to select the table. So here we have a raw file table. So let me use that one. Go to on mapping. So mapping succeed. Now click on OK. So we have done our loading part. So data will be loaded from flat file to already be destination. But this is static. So we just want to make it dynamic. So for that we need to declare three variables. So go to one variables one two and three in the first variable. We just want to get the folder path. So we can use the folder path and this is for the source. So we can call it source. Then file name. So this is the file name and this is again for source. Then full file path. So we can call this as full file path and this is again for the source and data type should be string. Again data type should be string. Then data type should be string for all these variables. So here as we can see name suggest folder path source. So here we have this flat file. So this is the folder path. Let me copy this and go to one SSDT and paste it here. And here use backslash. Now move to one file name. So let me use the file name. So here let me copy this file name and go to one SSDT and paste it here. Then full file path. So it should be folder path and file name. So we can concatenate between these two variables. So folder path. Then we can use add sign with the file name. Now evaluate that looks good. Click on okay. So we have done our source file path folder and full path here just make it dynamic. We can go to one flat file connection manager. Then go to one properties inside this expression. We have property with the connection string in the connection string. We just want to get the connection string from this full file path. Now evaluate it. Now it looks good. Click on okay. Again okay. So we have done it. Now let me execute this and we'll check data will be loaded into the table or not. So go to all SSMS and here let me execute this query. As of now we have 348 rows. Now go to one SSDT and try to execute this package. So it is executed successfully. So initially as we seen we have five records in the flat file. So go to one SSMS and here it should be 353. So it is 353. That looks good. Next thing we just want to rename the flat file. So for that we have to use file system task. So drag and drop this file system task and let me connect with the data flow task and just rename this with the renaming the file. So that is good. So here for renaming the file as we can see we need to take it two things. So let me select with the rename file. So here as we can see source connection and destination connection. So as of now we have source connection with the full file path. So on that we are having this full file path. But for the destination we have the folder path. But what would be the file name? So for that we need to create one variable and where we can calculate the file name new. So let me call this as file name new and here let me select with string and go to one expression. In the expression what would be the destination file name? So for that I have already written the expression. So let me copy this and paste it here and I have explained about this expression in the dynamic character manager video. So you can prefer that one. So evaluate that one. So here this is the file name and this is the year then month and then day. So as we can see in the expression we are going to calculate the year from this get date. Then we are going to calculate the month from this get date then day from this get date and dt wsdr comma 4 is going to convert this value into the string. So this is very straightforward. Let me click on okay and we have done with the file name. But in the file system task for the renaming file it was asking for the destination connection. So for this connection we need to declare one variable and let me call this as full file path new and here let me select with string go to here and as I told you file is available in the same folder. So we can use the folder path. So folder path source then plus after that file name new we can evaluate. So that is good. Click on okay. Save it and close it. Now double click on this file system task and here let me select operation as a rename file. After that here we can see we have option is source file path variable. Yes we are holding the value in the variable. What would be the variable name we need to select over here. We are having full file path source. Next go to on the destination we are holding the value into the variable. So we can click on true after that let me select with the destination variable name that is full file path new that is good. Click on okay. So now if I execute this package now so this file should be renamed and data will be loaded into the table. So as we seen data is already loaded so we can disable this and go to over here. So now I just want to execute this package. So this file should be renamed. Now click on start. So as we can see it is executed and file is renamed. So that is good. Now stop this package. So we have done two part of this package. So next part of this project is move file from the source folder to archive folder because data is loaded successfully. So for that let me disable this rename and drag and drop one more file system task and this is to move in file from the source to archive folder. So let me rename this with move file. Now I just want to move that file which we have renamed. So for that let me double click on this and here in the operation I just want to select with the move file. Again we are having the source connection and destination connection. So for that as of now we have source connection. But in the destination connection we need to select the folder path where we want to move that file. So that should be this archive folder. So let me copy this and go to SSDT. Let me declare one more variable and this time folder path for the destination. Now data type that should be string value is this and let me use this backslash because here we are using this backslash. That's why in the expression we don't need to declare this backslash. Otherwise in the expression we need to use double black slash instead of single. So we have done this now double click on this and here in the operation we need to select move file. We have the values into the variable. So we can click on true in the source full file path new. That is good in the destination. Here we just need to select the variable which is having folder path destination. That is good click on okay. Now as we have renamed that file so once we will execute the package that file will move from source folder to archive folder. So let's have a look of this. So now let me try to execute this package. So a second file will move to archive folder. So click on start. So as you can see package is executed. This file is moved from this source folder to archive folder. So we have done this. So just to stop this package and let me disable this move file. And what would be the next? For next we just want to give this renamed file. So what is this? So as we can see we have file in the archive folder. So I just want to give this archive file. So how we can do that? So for that we have to use execute process task as I have discussed in the SSIS tutorial part 65. Now let me connect with the move file and double click on this execute process task. So here as we can see we are having executable then argument and working directory. So we need to take care about above three. So first is executable. So here we need to specify the executable by which we want to zip the file. For that I am going to use 7GP. So we need to specify the full folder path with the file name of the EXE file of the 7GP. So go to on the program files and where we can see and copy the EXE. So go to on C drive then program files then 7GP and here we are having 7G.exe. So let me copy this and go to on the ssdT and paste it here. So this is the executable by which we want to zip the file. And here in the argument we need to define A then minus T 7G then zip the file path with the file name and then the source file. So this is the argument we need to define inside the argument. So for our case that is not static. So we need to define in the dynamic. So go to on expression and here go to on this and here we have argument go to on expression. So inside this expression we need to define that one. So for that we can select A after that we are having plus minus T 7 Z. Now we can evaluate that is good but we are having doublequat inside this minus T 7 Z. So for that how we can use doublequat over here. So for that we need to use backslash after that we can use this doublequat. So it will be going to use as it is. Next here we can use backslash after that one doublequat. Doublequat is good. After that we need to define the zip file name. So how we can define that one. So we should have that folder path destination. So we can drag and drop this one. And so here we are having this destination folder path but we are having the space. So how we can use that one for that we can use this space. Now evaluate that is good but we should have a doublequat over here. So we can again use backslash then doublequat. But we are holding the space between this. So let me remove this space and put the space over here. Now it looks good but here we are having the destination folder path. We need to define the destination file path. So for that we can use plus then the zip file name. So as we remember we are having the new file name. So this is the new file name. But in the new file name as we can see we are having the file name with the dot txt. So instead of dot txt we are wanting dot zip. So how we can do that. So here we just want to left comma then length of this variable and minus three. As we can see we are having this. Now let me use this plus and this time want dot zip. So that is good. And here again we are wanting a doublequat. So we can use backslash with the doublequat. So that is good. So next I just want to specify the source file name with the path. So how we can do that. So backslash with the singlequat but here plus. So that is good. And here we need to specify that one. So how we can do that. So for that we are having folder path destination. So let me drag and drop this folder path destination. So it is having folder path destination. Then we should use file name. So we have file name new. So drag and drop this one and evaluate. So now we can check we are good. Now let me use plus after that backslash with the doublequat. Now it looks good. Let us have a look of again about this expression. So here as you can see we have a plus then minus t7z then we are having jibbed file name with the file path. So here as we can see folder path destination that is our archive folder then this is our file name but here we are having extension with txt. So we just want jib instead of txt. So for that we are using left all the character except txt and then we have concatenate with dot jib. That is good. After that we are just holding our source file path. So that is this one. Now click on okay again okay again okay. So we have done about this once we will execute this package then that file should be jibbed. So let's have a look of archive folder and here we are having this now let me execute this package and have a look. So as we can see package is executed successfully and file is jibbed. Now stop this package but here as you can see inside this archive folder file is jibbed but we are having the original file as well. So we just want to delete this file after jipping the file. So how we can do this? So for that go to our file system task. Let me drag and drop this and connect with execute process task. Double click on this. Go to on operations. Let me select with delete file. So here we need to select the folder path with the file name. So file is available inside this archive folder and file name is this. So we are having two variables. First is holding this archive folder path and second is holding this file name. So for deleting this file we should combine these two. So how we can do this? We can declare one more variable where we can combine. So let me declare one more variable and in this variable we will combine our destination folder path with the file name. So let me call this full file path for destination. And it should be with string. So we can select as string. Go to on expressions. Inside this expression we need to combine folder path destination plus file name new evaluate. That is good. Click on OK. Close this. Now double click on this file system task and this time I just want to delete the file. So click on true and here I just want to select the variable full file path destination. Now click on OK. So once I will execute this package then this file should be deleted. Now start. So as we can see package is executed successfully and file is deleted. Now stop this package. So we have done this project and now we have enabled this. Now go to on file. Let me delete this jibbed file. Now go to on source folder and here we have flat file. So this is flat file. Let me make two copies for this and let me rename this with the flat file CD derived column. So once I will execute our package this file's data will be loaded into C crossover table. After that it will move to archive folder and inside archive folder this file will be jibbed. Now go to on ssdt to execute the package. So before going to execute this package go to on ssms and here I just want to check how many number of records we have in this table as of now. So we have 353. Now execute our package. So package is executed successfully as you can see stop this package. First of all go to on ssms execute. So we are having 358 records that is good. Go to on folder and inside this we don't have that file flat file CD derived column. So go to on archive folder and inside archive folder as we can see we are having that file and it is renamed but here we are getting two dots go to on package. So directly on this execute process task go to on expressions and go to here click on this and here as we can see we are having dot jib. So we need to remove this because we are going to take only three characters that is txt. So click on ok again ok again ok. Now save this go to on file just delete this go to on flat file folder and here let me rename this now go to on ssgt and try to execute this package one more time. So as we can see package is executed successfully now stop this package go to on folder and here we don't have the file with the flat file CD derived column go to on archive folder now here we have dot jib file now let me try to open this jib file. So go to on 7 jib open archive. So this is the flat file we have double click on this. So we are able to open this file. So it looks good. So this is very simple project but you need to take care step by step if you have any doubt in this project then you can do comment and I will try to respond on your questions. So thank you so much for watching this video. If you 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. If you want more project then do comment I will try to make more videos on the project. So thank you so much.