 Hey guys, welcome to SSIN intact to see this side and this is continuation of SSIN project. So this is part 6 of SSIN project and today we are going to see one more real-time project. So here basically we are going to load the data into the SQL Server tables from Excel and DB. So here we are having two different sources. First is the Excel and second is the SQL Server table. So we need to get the data from these two sources and load that data into SQL Server table. We need to implement the package with the data load option. So what it means? So it is basically the incremental load. So if your source have incremented data then we need to load or update or delete only that data which is not available in the destination. Third we need to deploy that package and after that that package should be executed every 30 minutes. So this is a requirement that we need to do. So before going forward if you haven't watched these videos in SSIS tutorial video series, so I would strongly recommend to watch all these videos where you can search for the incremental load, lookup transformation, lookup scenarios and cassette transformation. So all these videos are required for this video. So you can directly go in the YouTube and here you can search for SSIN intact and after that you can see this so you can directly jump on the SSIN intact and after that here you can go in the playlist. So here you can find out a playlist with SSIS tutorial. So that is available right here. So you can go and you can try to find out about these videos. So those videos are available in between these 70 videos. So you can navigate and you can watch all these videos or I will provide direct link for these videos in the description of this video. So let's get started with this video. So here we are having two different sources, first source that is Excel source. So basically as you could see here we are having this folder. So inside this folder we have these two Excel files. So we need to load the data from these two Excel files. First is containing for the US customer and second containing for the Bangladesh customer. So we need to load the data from these two Excel files. Now the second source that is our SQL Server Table. So inside this you can see this is our SQL Server Table which is again containing for the employee information. So we need to load the data from this employee table to this employee destination table and here we are having the same columns that you could see inside the source. Only one column is additional that is deleted. Now you can go and you can check for the destination table of the Excel source. So Excel source you could see we are having this destination table. So all these columns are available in the Excel file except this customer ID. So we need to load the data on this customer ID at the runtime and this column is identity column and the primary key of this as well. So it will be going to auto generated at the runtime. Let me open this Excel file. So you can quickly go through about the source data. So this is a source data that you could see. So we are having only a single seed on this and it contains the information for the customer of Bangladesh. Let me close this go to on the SSDT. So inside the SSDT first I am going to load the data for this Excel source. So let's try to configure this for the Excel source. So first we need to use the for each loop container. This is because here we are having multiple files. So we should use the for each loop container. So one by one all these files will be going to process. So go to on the SSDT again. Let me try to drag and drop from this SSIS toolbox. So here you could find out for each loop container so we can drag and drop. Here you can rename this and let me call this as Excel source. Now we can make it bigger like this. So double click on this. So go to on the collection and inside the collection we are going to deal with this file. So here we should use for each loop container for the file enumerator. So the second option that you could see you can select that. Next it is asking from which folder we need to loop through. So that we need to specify right here. So we can directly go on that folder and let me try to copy this path. Now we can paste that in this folder like this. Then inside the files we can specify which files we want to loop through. So we want to loop through only the Excel files so that we can specify like this. And we want to have the full qualified path. This is because while we are going to create the connection with the source then we should have the exact folder path with the filing. So that is why we are going to use this fully qualified. Now we can go on the variable mapping. Here we can create a variable and this variable we can call this as customer source. We can click on OK. So in the index we are going to return only a single value that is the full qualified. So that is why the index that should be 0. Now we can click on OK. So we have done with the for each loop container. Now we can use the Dataflow task to load the data from Excel to SQL server table. So we can drag and drop the Dataflow task in between this for each loop container. Let me double click on this Dataflow task. Or before that we can rename this like load data up insert. So it means update and insert both are going to happen on this load data Dataflow task. Let me double click on this. Now the first thing that we need to get the data from Excel file. So here we can see the other sources. We can drag and drop this Excel source not double click on this Excel source. Here we need to create the connection. So let me click on new. So first time I am going to create the static connection will be going to chain So we can copy this path. We can browse. Then we can select any one of these two files. Let me click on open. Click on OK. So now we need to check from which seat we want to get the data. So we have only a single seat that is seat one. So we can select that after that go to on the columns. So all the columns we can see and we are required to have now click on OK. So now we have successfully configured for the Excel source. Second we need to use the lookup transformation. So if you haven't watched the lookup transformation. So first go and try to watch the lookup transformation. Let me try to drag and drop the lookup transformation. So what lookup transformation will do? It is going to match your source data with your table. If that is going to match then lookup transformation will return to output. First for the match output and second for the no match output. So let us try to configure this. Let me connect this data pipeline to the lookup transformation. Now double click on this lookup transformation. So in the CASA mode we are having full CASA, partial CASA, no CASA. So full CASA means all the data which we want to get for the reference table that is lookup table will be going to load in the CASA and after that will be going to start the lookup. Inside the partial CASA it is going to check at the runtime. For example in the source we are required to have ID 1. So it will go and try to get it from the database and after that load that inside the CASA. In case of no CASA here the data that is not going to available inside the CASA. It is going to get at the runtime from the database. So actually I have created a separate video for the detail information for the CASA mode inside the lookup transformation. So you can check that video. So inside the connection type, so our data which is available inside the OLEDB connection manager. So we can select that and after that we can go the connections. So now it is asking a connection which will be going to refer in your SQL server table. So let me click on new. I have already created the connection but I am going to delete this and try to create one more time. Click on new. Then here we can specify the server name. So here this is our server name I can select. I am going to use the window authentication. Here I am going to select the database. So I want to create the connection with SSIS underscore test database. Now let me test connection. So test connection succeed. So we can click on OK. So again OK. So connection is successfully made. Now here we could see we are having this table which is customer detail table. So in this table we want to load the data. So first we are going to check the data whether data is available on this table or not. So here we can select that table. Go to on the columns. So from which column basis we want to check that. So that we could see the customer name. So we can drag and drop and try to match with the customer name. Let me select all these. So we are required to have all these columns. So everything looks good on the lookup transformation. One thing that we need to check here we are having a property specify how to handle the rows with no match output. If any rows are going to inside the no match then what we want to do? We want to redirect those rows into no match output. So we can select that and click on OK. So lookup transformation is done. Now we want to load the data into SQL Server table directly. If data is not available that is no match output. So let me try to drag and drop this OLEDB destination and connect this lookup transformation. So it will ask we want to load the data if no match output is there. Click on OK. Let me double click on this OLEDB destination. And here the same connection that we have created for the reference table. So it will be going to use because your table is available on the same database. So let me select that table that is again for the customer detail. So we can select go to on the mapping. So all mapping got succeed because the source table name and destination table names are same. Here we can see customer ID which is not available in the source. So this is the identity column. So while we are going to insert the data into all these columns. So customer ID will be going to generate at the runtime. Click on OK. So we have done with the destination. Now if data is going to match with the lookup transformation. So in this case first we need to check whether we have any changes on the data. That is your other columns except the customer name. It might have we have any change in the customer in the address city state of country. So if we have any changes on this then again we want to do the update. So how we can do that. So similarly we can go here and try to use the conditional split. So that is available inside this common. So we can drag and drop this conditional split and try to connect with the lookup transformation. So this is for the match output. Now let me double click on this conditional split. So here you can see we are having all the columns that is your source and lookup transformation. So we want to check if your address which is coming from the excel source. If this value is not equals to your lookup address value. Then we want to move that inside the case one. Here we are going to check whether this value or again we are going to check for the city. If this is not equals to lookup city. Close the bracket or here we are going to check for the state as well. So let me drag and drop this state which is not equals to lookup state. Close the bracket here not equals to country. So we need to check for all the columns those are available right here. If we have any changes on all these columns then that will be going inside the case one of the conditional split. So it seems good we can click on okay. So if data is not going to save for all these columns or any one then we need to use the update command. So we can drag and drop this OLDB command transformation. It is going to execute a row by row. So let me double click on this. So again here it is asking for the connection manager. So we have already created the connection for this DB. So we can set that go to on the component property. So inside the component property we could see an option for the SQL command. So here we can specify the SQL command. So I have already written the SQL command for update of this customer detail. Let me copy this and paste it here. So this command is very straightforward like we are going to update this customer detail table and setting for this customer name is question mark address question mark, city question mark, state question mark, country question mark and customer ID with the question mark. So we are written this SQL query because we are going to get the data for the customer ID and customer name address. So all these columns from the conditional split. So we are going to put at question mark click on okay. Go to the column mapping. So as we can see so first that is the customer name. So here we need to map. So our source customer name we can map with the parameter 0. Second we can check this is for the address city and state. So second will be the address for the parameter 1 city for the parameter 2 state for the parameter 3. And after that country for the parameter 4 and for the fifth parameter we should have the customer ID. So we can drag and drop like this. So all these mapped successfully. Now we can go in the connection manager and click on okay. So everything looks good. So this is done for the up insert. It means update and insert for the Excel source. So next I am going to load the data into the SQL server table and from the SQL server table which is for the employee data. So this is for employee source and this is for the employee destination. So here we want to load the data. So go to on the ssgt and here before going to use the data flow task. Let me use a sequence container. So what is the sequence container? Sequence container is nothing but it's a logical grouping of the task. So for example here we are going to group the task and this for the employee load. So that's why I am going to use this sequence container. So this will be going to treat as a single unit. So whatever the task that we are going to inside will be going to treat as a single unit. So let me use this data flow task. So again this data flow task will be load data which is our employee data and this is for the again up insert. So this is for the update and insert. Let me double click on this and here we need to get the data from the SQL server table. So we can drag and drop the OLEDB source and we need to load the data into SQL server table. So destination will be OLEDB destination. In between we need to use the lookup transformation to check the data whether data is available in the destination or not. So we can use this lookup transformation. If data is available then we need to check biogic conditionally split. So we can drag and drop if data is available for other columns. Then we can go and use this conditionally split and last we need to use the OLEDB command. If we are having any changes in the other columns of the primary key. So we can use this OLEDB command to update that. So let me quickly configure all this. Let me double click on this. So the table which is available inside the SSUnitech database which is your employee source and employee destination table. So we need to create a new connection. So click on new and click on new again. Here again we need to use the same SQL server. So we can select this from this drop down. We can select this from this drop down. So this is our SQL server and the database which is SSUnitech. Now we can test the connection. Succeed click on okay again okay again okay. Now we need to select the table. So our table which is employee source table. Go to the columns so we are required to have all these columns. Click on okay. So we have done with the OLEDB source. Let me connect this with the lookup transformation and double click on this. So here the reference table which is available on the SSUnitech database. So we can utilize this. Let me select the destination table which is employee destination and this will be going to treat as a reference table or lookup table. Go to columns. We want to match by using this employee ID. So we can connect this. Let me select all these columns and click on okay. And here we can do one more thing like inside the general as specified how to handle rows with no match entries. So redirect rows to no match output and click on okay. So here we can see no match output will be go inside the OLEDB destination. Click on okay. And if match output that will be go inside this conditional split. Now double click on this conditional split and try to configure this. So inside the columns as we could see we are having all the columns. So first we can see for the employee name. So here I am going to check if the employee name from the source is not equals to employee name from the lookup table then or the second we can check for the salary is not equals to salary in the lookup table. So we can drag and drop like this. Close the bracket or here we can check for the department ID as well. So department ID is not equals to department ID from this lookup table and we can close the bracket. So everything looks good. Click on okay. So if we are having any changes so that should be the case one. So we can connect that case one inside the OLEDB command. So everything looks good for this. Let me try to configure this OLEDB command as well. So double click on that. Here we need to select the connection manager. So the connection manager which is SSVTEC go to the component property. So here we can specify the SQL command. So I have already written that one. So let me try to copy this and paste it here. So this is going to update this EMP destination table and updating for the employee name, salary, department ID along with the employee ID. Click on okay. Go to the mapping. So inside the mapping we can see we have this employee ID. So we can match this employee ID with the last column. So let me go in the component property again. So first is the employee name, salary, department ID. So first is employee name, employee salary, then department ID and last will be employee ID. So everything looks good. We can click on okay. Now we have done for this. Now we need to configure this OLEDB destination. Double click on that. So utilize the state collection manager. Only select for this employee destination. Go to the mapping. So all mapping got succeed. Click on okay. So we have successfully configured for this up insert of the employee data. Let me try to configure for the delete as well. So let me use the Dataflow task and let me rename this with the delete data into EMP table. So we can connect to this. So one by one it will be going to execute. Let me double click on this. So here we are going to check from your destination table to your source table. So your source will be destination and your destination will be source for the delete case. So here we can go and get the data from the SQL Server table, that is your destination table and double click on that. Here we need to select for destination table and click on okay. So we have done for this. Next we need to check with your source. If data is our label in the source, then we are not required to do anything. If data is not our label in the source, then we want to delete the data in your destination table. So here first we can use the lookup transformation to check that connect with the OADB source. Now double click on this lookup. So full cache and OADB connection manager is okay. Go to the connections. Here we can select the source, go to the columns and check for the employee ID. So here if your employee ID is going to match with the employee ID, then we are not required to do anything. If it's not matching, then we want to delete the data from your source. So here your source is your destination. So how to handle the rows with the no match entries? Again redid rows to no match output. Click on okay. Now we can use the OADB command. So we can drag and drop OADB command and correct with the lookup transformation. And this is for the no match output. Click on okay. Double click on this. And here we need to select the connection manager. So connection manager will be ssunited database. Go to the component property. So here we need to set your SQL command. So I have already written that command. So this is the command. Let me copy this and paste it there. So here we are not going to actually delete the data. So if you go and check inside the destination table, so we have a column which is deleted. So by default it's false. And if we are going to delete, then we are going to update this as true. So that is one for that employee ID. Click on okay. Go to the column mapping. So only employee ID will be going to map in this parameter. Click on okay. So we have done for this employee data load, insert, update and delete. Here last we need to check for the delete case as well. So for the delete case, if you go and check inside this lookup transformation, only two properties. First for the cast a connection manager and second for the OADB connection manager. So our source which is the Excel source. So we cannot use directly inside the connection type which is the Excel. So Excel is not available. So indirectly either we can use the OADB connection manager or we can use the cast a transformation. So the problem with the cast a transformation if we are going to use. So here we are going to look through with all the Excel seats. And if we are going to put that value inside the case, then while it is going to again checking for the second table, so your cast a connection manager will be going to fail because it is not going to allow us to load the data into the cast a twice. So how we can do that? So for this either we can create a table in your SQL server and where we are going to dump the data for all the Excel files. After that going to use that SQL server table as a source or we can create any flat file connection or any Excel where we are going to dump the data directly from these Excel. And after that going to utilize that as a delete case. So whatever you want to prefer you can use. Here I am going to use to load the data inside the SQL server table directly. So how we can do that? So here as we can see we are having this staging table that is the customer detail underscore a staging. So in this table we are going to load the data from all the Excel files and checking that data for the delete case. So before going to execute all these first I want to truncate this table. So how we can do that? So let me use the execute SQL task. So this execute SQL task will be going to execute your SQL queries and command. So let me double click on that. And here you can see all the properties. So this is the result we don't want to return anything from our query. We are only going to truncate the table. So we can ignore that. Then it is asking for the connection type. So here we have multiple options. But I am going to use the OLADB because I want to truncate the table inside the SQL. Then it is asking for the connection. So here we have already created the connection for this SSIS test. So we can use that. After that the source type which is the direct input or from any variable or the file connection we are going to use. So I am going to use the direct input. So here we can write the truncate statement. So we can write this truncate table. And after that we can write the cust detail underscore staging table. Let me click on OK. Click on OK. Now here inside this dataflow task let me double click on that. And here we are going to use the lookup transformation. So before the lookup transformation I want to load the data into that table. So how we can do that? We want to replicate this Excel source. So how we can replicate that? So let me give this connection. And here let me use the multicast. And after that connect that multicast and use it there. So multicast is nothing. It is going to create the multiple copies of your source data. And you can use according to your requirement. So here I am going to dump that data into SQL server table. So we are going to use the OLADB destination. So let me connect with the multicast. Now double click on this. And here let me select the connection manager which is SSIS test. And after that we can select that staging table. Go to the mappings. So mapping got succeed. Click on OK. So now everything looks good. So after this loop we can use the dataflow task. And this dataflow task we will be going to use for the delete case of the customer data. So we can call this as delete cust data. So here our source that should be your destination. So our destination that is SQL server table. So we can drag and drop this OLADB source. Let me double click on this. And this is available inside the SSIS test. Now here we can select. So our destination is customer detail table. Click on OK. Now we need to use the lookup transformation to do the lookup whether data is available at the source or not. So we can drag and drop this lookup transformation. So this is the same thing that we already did for the employee data. So I am going to double click on this. Go to the connection. And let me select the SSIS test. And here it is going to check with the source data. So our source data this staging environment. Click. Go to the columns. And the mapping going to succeed with the customer name. Not click on OK. So we have done with this. Next if data is not available in the source then we want to delete the data directly. So for that we can use the OLADB command transformation again. Let me connect this with the lookup. And here this is for the match output or no match output. So obviously this would be for no match output. If the lookup transformation here you need to specify a redirect rose to no match output. So now it looks good. So double click on this OLADB command. And let me quickly configure this. So here is the SSIS test. Here we need to specify the command. So I have already written that command. So let me try to copy that. So paste that here. Click on OK. Go to the columns. So customer name should be going to parameter map. Click on OK. So now we have successfully configured all these. Let me try to execute and quickly look the table. So we can click on start. It will take a little bit of time. So everything is looks good and executed successfully this practice. Click on stop. Go to the database. And here if we can see. So we should have the data for all the extra source. They are for the Bangladesh and we should have the data for. But here we don't have the data for other source. This is because if you go inside the SSDT. So this source is going to. So this extra source as you can see it is going to. Indicate for this Bangladesh file every time. So that's why you have the data in this table twice for the Bangladesh. But we don't want to make this static. We want to make this as dynamic. Cancel this here. Go to on the property and inside the property we have a property with the expression. So inside the expression. Now we can see the property for the excel file path. So this excel file path remember we are going to get. From the for each look container evaluate that. And click on OK. Click on OK. Here we are getting the error on this excel source. This is because inside the variable this is blank. So by default we can specify the path. Of any one of this file. Inside the variable. Now we can save this. Now let me double click on this and try to refresh this. So now everything looks good. Now let me try to execute this package. And check about the data. So click on start. It will take a little bit time to execute. So everything looks good. Click on stop. Go to on the SQL server. And here let me refresh this. So we have the data for the Bangladesh. And we are also having the data for the US as well. So now go to on the source site. And inside the Bangladesh. Let me try to change this as bn bn. Let me save the source. So if the source we did not make any change. Everything remains same. Only we have renamed that as can to can bn. Now go to the SQL server table. And this was the staging table. So let me quickly check for the main table. So this is main table. Let me refresh. So can i n i can see. Let me try to execute. So it is executed successfully. Stop this practice. Go to the SQL server. Let me refresh this table. So here we should see the can bn. That you could see right at the bottom. So this is for that way. So this we have deleted. If you can see. So here your table id got changed. This is because the previous entry got deleted. And it is inserted a new entry. Because it is checking on the customer name. So customer name which is can bn is not available in the table. So it got instructed and can i n was not available in the source. So that way that was deleted. So let me go here. So now let me make the change on this state for this teri i n to something like dhanka. Let me try to execute this package. So this should be going to update whatever the value that is available at the source. So package got executed successfully. Let me stop this package. And go back to here and try to refresh this. So here unfortunately it is not updated. So go to the package again. And go to this and check about this oledb command. We could see this column mapping. So inside the column mapping as we could see everything is mapped with the lookup. So it should not be mapped with the lookup. It should be mapped with the your source. So let me update this this country as well. Customer ID is okay. Now we can click on okay. Save this package and try to execute it again. So it should be going to execute successfully. So as you could see stop this package go back to here and try to refresh this. So this got updated as we have the previous value. So I hope guys you have understand about how you can set up all the packages and all. Now I want to deploy this package inside the SQL Server table. So the next step that you could see the deployment of the package. So let me try to click on that click on the deploy click on the next. So it will be going to ask your source. So your source you could see this is your source. So let me copy this and go to here and paste that then it is asking for the path. So we can browse. So here if you can go and try to search out for integration service catalog. So here we don't have any folder with the name of this. So let me try to create a folder either here or we can directly create the folder right here. So click on new folder and let me call this as loadxl and tb. So we can select that click on okay. So we want to deploy this package on that folder click on next click on deploy. It should be going to deploy successfully. So it will take a little bit time now we can see everything got succeed. We can close. So now if we can go and inside the SQL server let me try to refresh this. And here we can see we have this loadxl db inside the project we are having this package. So if you want to execute this package so it can be executed by right click and execute from here. So everything looks good. Go to the advance it is going to support for 32 bit. So we need to select this checkbox now click on okay. It will take a little bit time. We can see the report. Yes we want to see the report. So it got succeed. Let me close this and if you want to test that. So let me close this customer detail. If you can go in the source and in the destination let me refresh this. So we are having all the data with that destination. Let me rename this as 14 at the source. So in the destination we should have one more entry with the 14 and this 13 will be going to mark as false. Let me try to execute this package again from this and advance. Check this checkbox and click on okay. It will take a little bit time. We can see yes it is running. We can refresh. So it got succeed. And if we can go and try to check for this employee destination. Let me refresh that. So here 14 got inserted and 13 is marked as false to true. So it is executing successfully as you could see. Now go to on the ssd team and here I want to configure this with the error handling. So if anything will be going to fail on this I want to suit an email. So how we can do that go to on the event handlers and inside the event handler. We could see the option for the on error. So if any error will be going to happen on the package then directly we want to send an email. So for sending the email either we can use the send mail task or we can use the script task. So these two will be going to use for that. So I have created a separate video for this send mail. So you can watch that video. Now I want to deploy this package again. So right click click on deploy next. So that is okay click on next then deploy. So here package deployed successfully. Now we can close that. So go to on the ssd team again if we can go on this. So we have already tested for this. And next the last step that we want to schedule this package. So it will be going to execute every 30 minutes. So how we can do that. So for that we can go inside the SQL server agent and here we can create a new job. So that job will be going to called as load data from your excel to DB and it is going to execute on 30 minutes. So we can call this. Go to on the step and here we can add a new step. So this new step will be going to add here the step name is required. So the first step name that will be going to have the execute on 30 minutes. So that could be this second the type that is obviously SQL server integration service packages. So run as SQL server agent service account. So because we are going to reduce that inside the SQL server agent. Next we can have the package source that is or your SSIS package store or the SQL server or the file system or the SSIS catalog. So obviously it is available inside the catalog because we have deployed the package right there. Now let me select the server from this drop down. We can select and after that which package we want to execute. So I am going to use the windows authentication. Click on this SSIS DB and load excel DB. This one this package click on okay. So we can again click on okay. So here let me edit this again because remember while we were executing that. So inside the configuration inside that once we need to select this 32-bit runtime option. If you are not going to set this so your package got fit. Click on okay. Go to the schedule. Click on. So here you can schedule execute every 30 minutes a recurring and here instead of weekly or daily or monthly we can directly select daily option. Daily frequency occurs every one hour or here we have the minute. So occurs every 13 minutes. So starting time that you could specify this is 320. So let me configure this as 322 and this should be PM. So everything looks good. It is going to start by today no end date. So it will be going to execute forever. So let me click on okay again okay. So your package is scheduled successfully. Let me right click on that and here we can see the view history. So as of now it is not executed anytime. So we need to wait one minute is remaining. So after one minute it will be going to execute successfully. So let me pause this video. So now it's 322. Let me refresh. So it is a start execution. So we need to wait few seconds. So here we could see it is executed successfully as you can see this checkbox. So in the steps we could see by clicking on this one. So this is the step that got executed successfully. Now let me close this. So I guess you have understand how you can design this package. How you can deploy this package. How you can schedule this package and how you can look all the Excel files at the runtime and inside the event handler how you can set up the send email option from right there. And one more thing I want to let you know inside this job you can go and here we can see the alert option. So inside the alert option schedule notifications. So these options you can use. So inside the notification option while we are going to check on this checkbox for the email here we don't have configured any option. So we are unable to see anything. So here we can directly send an email by this SQL server job. So when jobs succeed when job fail when job complete. So these three options is available. So while we are going to execute complete or fail we want to send the email. So directly we can send it from here. 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 icon to get the notification of our newly uploaded videos. See you in the next video with new project and concept.