 Hello welcome to SSUni Tech, so say this side and today we are going to see one more real-time SSIS project. So in this video we will also see the best practice that we should follow while designing the SSIS package. Let's go to another slide and we will understand the requirement first. So here the requirement is we want to load the data into the SQL Server tables from your Excel files. So this is the actual requirement. So we have already discussed how we can load the data from Excel file to the SQL Server tables. So that is not a big deal. Here the catch is we have to handle this case. So the case is if two users are executing the same package at the same time or the first user has executed the package and second user wants to execute the package in between. Then the second user should not be able to execute the package. So this is the requirement. So how we can implement this requirement? We will see below image. So here first we are required to check whether the package is running or not. So how we can check that? So for that we should be having a table and their table will be keeping the information of SSIS package executions. So this should be having something like the package name then they start date and end date of the execution of the package. Here we will see first it is checking whether the data is available in this table or not for that particular package. If data is there then we should be checking the end date. If the end date is null it means the package is currently executing. So on that scenario we can go and we will reflect an error or we log that error in some of the files or we can send the email whatever is your business requirement you can implement. If package is not executing then we can insert the data into this log table and we will be keeping the package name here and then the package start date and end date will be null. So after your load will be completed which is loading the data from excel file to the sql server table then on the completion of this we will be going to update this end date. So either this will be completed with success or failure your end date will be updating on this execute sql task. So this is what we have to implement. So for that we are required to have few of the store procedures. Let me go into the sql server management studio and we will try to create those. So here first we are creating the table so that table will be having total four columns first is the id as identity column then the package name start date and end date. Let me try to execute this query to create the table. Table is created successfully. Now first we are required to have one of the store procedure on that we will be checking whether package is executing or not. So that we can see in the first level. So here it is checking your package is running or not. So we will be taking as an input parameter of the package name and after that we will be reflecting as an output with yes or no. If package is running then we will reflect as yes otherwise no. So the same thing we can see here in this package it is taking as input parameter of package name and after that here it is checking on your table for that particular package if the end date is null so we can say that your package is executing. So here we can see yes and at the same time we can see no if your package is not executing. Let me try to execute this and we can see your store procedure is created successfully. Now let me go into the Bujjiro studio and here first we have to use the execute SQL task. So that execute SQL task will help us to check your current package is executing or not. So we can drag and drop this. Now here let us come configure this. So double click on that. Here we have to create a connection. So let us try to create a connection. So click on new connection. Here we have already created a connection with the database. So we can utilize the same. So as we can see this is the pointing current data. So let us click on this. Now here the SQL server type. So we can see the source as an direct input and the SQL statement. So here we can specify the statement. So this is your execute statement. So let me try to copy this. Here we can specify the question mark because we are getting this parameter from the package. So we can paste as it is and in the result set we will be getting only a single result set. So we can specify as a single rule in the parameter mapping. We can add a parameter here and here we can see the package name. So we should be having one of the system variable with the package name. So this is your input parameter direction will be input data type we can select as an worker or an worker. Here we can see this parameter name. So this should be your exact parameter name or we can specify as an zero. Here this is the parameter size. So either we can specify the size or minus one will take as n where can max. In the result set we will be having a single result set. So click on add. Here it will be returning only a single column. So we can specify as an zero and this is the status. So we have to click a new variable here and this variable will be saying as yes or no. So we can wait. Here we can see the name. So let us call this as status. Then here the value as string and everything is okay. Click on okay. Now we can click on okay. So this is checking whether your package is executing or not. If your package is executing then it is checking inside your table and we will get yes or no from there. Next we have to use another execute SQL task and that execute SQL task will be connecting with the first one. So let us go in this precedence constant and in this precedence constant we can see the evaluation operation instead of constant. We can go with the expression and the constants. So it will be executed with success and your variable which is the status. This variable value should be equals to equals to as no. We can evaluate. This time this should be false as we can see. Let me click on okay. Again okay. So we have configured the first checking of SSIS package execution and here it should be inserting the data into your log table. So for that again we are required to have one stroke procedure. So let me go into the SSMS. As I have already written that SP let me explain how it will work. So here we can see it is taking two parameters. The first parameter will be the package name and second parameter will be your stage. So stage is saying something starting and ending. So here we can see the stage as yes. So it is starting your package. So here it will be inserting the data into this table. As we can see it is having only two values for the package name and the start time. Now here we can see it is going to get the package name as an input parameter and the start time will be your get date. So it is like the current date and time. If in case of the ending of your package like everything is completed your package is completed at the last we want to update the end date. So here we can see the stage as yes. So this time it will be going to update your table like your end date will be the current date and here it is checking your package name should be the package name and the end time will be none. So this is the SP. Let me try to execute this and it will create this SP. Now let me go into the SSIS and here in this package let me double click on this. Here as we have already created the connection so we can use the same and here let me try to call this stop procedure as starting. So first parameter will be the package name and we will get this from the package. So that should be your question mark and second will be your yes so that is starting. Let me copy this in the SQL statement we can paste that. Again in the parameter mapping we have to create one parameter and this parameter will say for the package name. So here we can see in the bottom side package name direction input is okay data type long so it should be having like n-wire care. Here we can see the parameter name so we can specify as n0 and here the size we can leave as this. Why we are specifying 0? Because we can see inside the stop procedure for the first input parameter will be 0 for the second that will be 1. So second we have specified the hard coded value so that is why we are not required to add anything. Let me go again here and in the result set so here we are not getting any result set we are only inserting the data so we can now what next we have to do next we have to use the data flow task. So this data flow task will be loading the data into the table so we can drag and drop inside this package we can connect you with the execute SQL task and here double click on this and we can go here and we will try to get the data from the Excel source and after that loading the data into the SQL server table so we can drag and drop the OATB destination. Let me double click on the Excel source click on the new connection and we can browse. So we can browse and we can select the file which is asia underscore 2021 something like that click on okay. Here we can see the seed name which will be customer so we can select we can preview the data under this so it should be having the data. Now let me close this click on okay. Now we can connect this Excel source with the OATB destination as we have already created the connection so we can see the OATB connection here. Here we have to select the table name so the table name which is the customer so we can select the customer details here go to the mapping so as we could see all mappings is succeeded. Now let me click on okay so everything looks okay till now the last part we want to execute the SQL task again so this time it will be updating your ended so we can connect with this and instead of this success this should be on the completion either your load will be succeed with the failure or success this execute SQL task two should be executed and this time this should be executed as end so we can copy this go to SSIs double click on this here we have already made the connection so we can use the same query we can specify parameter we can go and try to add a new parameter so this parameter is for package name so we can select the package name here next we can see the data type so this should be the end backer and here we can specify as an zero let me click on okay so we have successfully configured this but in case of the packages already executing we want to send the email so we can use the send mail task here if you want to log in some of the file then we can specify the log file here but I am not going to do anything I am just trying to connect with this script task and will show you let me double click on that so here instead of constant only should be expression and constant and here remember like on the first to go we were checking for the no here we are checking the yes if the packages are already executing then we can go with this so we have successfully configured this let me quickly go here and here we can see this table and let me try to execute this so as of now we don't have any data here now let me go in the SSIs package and try to execute it so once it will be executed we will see the data so as we can see this got executed successfully and here we will see the data in this so as of now end date everything is updated this is the package name start time and time everything is here I am just trying to update the end date so this is the end time as a null so it means this package is already executing somewhere so as we can see this time let me try to execute this package so what will be happening this time it will not go in the first way it will be go into the failure like the packages are already running so that will be executing the script task so let me rerun this package so what will be happening it will be going to execute the execute SQL task and this script task that you can see so here as I told you we can set up everything like the send mail task you can set up either you can set up some logging inside the text file or the SQL server table so whatever you want to do so this is the best way you have to design your package if your package could be executed by multiple users at the same time so I hope guys you have understood how you can design the package how you can execute everything I have explained here if you have still any doubt then you can comment your questions in the comment section I will try to response there