 Hello, welcome to SSUnitech. So, see this side and this is continuation of SSIS tutorial. So, today we gonna discuss about merge transformation. So, what is merge transformation? So, merge transformation is used to combine two sorted datasets into a single dataset. The rows from each dataset are inserted into output based on the value in their key columns. It also requires that the merged column in its should have matching metadata. Like if we want to merge two input datasets column one in our dataset one in column one in dataset two these two should have the matching data types. We cannot use them more than two datasets. This is our case one where we need to use sort transformation. Second we will understand without using sort transformation because sort transformation is fully blocking transformation. So, we need to avoid if we can. So, here we can see we have two input seats. Seat one contains the information of male employees. Second seat two contains the information of female employees. So, we want to merge and load in our LEDP destination. So, let's have a look of input seats. So, this is our male employees. As we can see it has employee ID, name, gender and salary. We have total four columns. Go to one female employees. Here again we have employee ID, name, gender and salary. So, we have total four columns. So, we want to merge these two seats data and upload in our LEDP destination. So, let's have a look of equals our table where we want to load the data. So, this is our merge statement table. So, initially we don't have any data. If we can execute our first query, then we can see we don't have any data and it also contains the four columns. First is employee ID, second name, gender and salary. So, we have total four columns. Now, go to one SSDT to implement this package. Here we need to add one more package. So, right click on this SSIS package and create a new package. And then we need to rename with merge. We are going to use sort. So, we can use sort. Now, inside this control flow panel, we need to use data flow task. So, we can drag and drop this data flow task and double click on this data flow task. So, here we need to configure our source transformation and destination. Our source is Excel file. So, we need to use Excel source. Double click on this Excel source. Excel source editor window will be open. So, here we need to make the connection manager. Here we need to select the Excel file path. So, go to one merge. First of all, I am going to make connection with made in price. Click on open. Click on OK. Here we need to select the Excel sheet. So, sheet one contains the data. So, we need to select the sheet one. Go to one columns. We want all the columns. Then click on OK. So, we have done with mail in price. We can rename this Excel source with mail. Second, we need to configure for female. So, we can rename with female. Now, double click on this female. Here we need to make one more connection for female employees. Select the file. Click on open. Then OK. Again, we need to select Excel sheet. Sheet one contains the data. Go to one columns. We want all the columns. Click on OK. So, we have done our source. Now, we need to configure our transformation. So, here we need to use short transformation. But I am not going to use short transformation yet. I am going to use directly merge. If we can drag and drop merge, then we can see over here, we have down arrow and up arrow. It shows that we need to use sorted input. If we are going to use unsorted input. This is for merge one. Click on OK. For merge two. If we can click, then we can see error message. It shows me that the input merge one and input merge two has short key position assigned to their columns incorrectly. It means we need to use short transformation to sort the input data. So, I am going to use short transformation. We can drag and drop short transformation for male and female as well. Then we can connect with male and female. Now, we need to double click on this short transformation for male. Here we need to sort with employee ID and click on OK. Then double click on this female. We want to sort with employee ID. Then again click on OK. So, now we have sorted input data for male and female as well. Now I am going to use merge transformation. So, we can connect with merge input one. Here we can see we don't have option for input three. We have option only for input one and input two. So, it has the limitation where we cannot use more than two inputs. So, as we can see error has been gone. Once we double click on this, then we can see we have output column name merge input one and merge input two. So, we are okay with it. Now, we need to configure our destination. So, our destination is LEDB destination. So, we can drag and drop LEDB destination and connect with merge. Now, double click on this LEDB destination. So, LEDB destination editor window has been open. We need to make the connection manager. So, as we have already made the connection. So, I am going to use that one. Here we have merge statement table. So, this is our merge statement table. Go to on mapping. So, mapping succeed. Now, click on OK. So, initially we don't have any data as we have displayed. Now, execute our package. As we can see 19,000 rows has been inserted in our table. If we can execute our selected statement, then we can see 19,932 rows has been inserted. This is our case one. So, in our case two, without using sort transformation. So, here we can sort data at source by using it sorted property true and set sort key position. So, how we can implement create one more package SSIS package new SSIS package. Here we need to rename our package with merge. So, here we need to drag and drop our dataflow task. Then double click on this dataflow task. Now, we need to configure our source transformation and destination. So, our source is Excel source. So, we can drag and drop Excel source. Double click on this Excel source. Click on new. We can browse and select for main. We can click on OK. Now, we can select our Excel sheet, sheet one. Go to on columns. We want all the columns. Click on OK. Now, we can right click on this. So, advance editor. Here we have input output properties. Here we have an option for is sorted. We can select this property with true. Once we click on true, then go to on output columns. In this column, we need to set the sort key position. Sort key position should be one. Click on OK. For female employees, we need to make connection, browse, female, click on open. Now, we need to select our Excel sheet, sheet one. Go to on columns. We are OK with it. Now, again, then show advance editor. Input output properties, Excel source output. Here, we need to select this is sorted with true. Then we can expand output columns. Employee ID. Sort key position should be one. Then click on OK. So, now we are getting the data is sorted at source. So, we can use this. Now, we can use our merge transformation. Drag and drop this merge transformation for male. This is merge input one. Click on OK. Then merge input two. As we can see, now click on this merge and we are getting the output column name merge input one and merge input two. We want to load this data in our OLDB destination. So, we can set up our OLDB destination. Now, we can connect with merge. Click on this. Click on new. As we have already made the connection. So, I am going to use that one. And again, we are going to use data in merge transformation table. Go to on mapping. Mapping succeed. Click on OK. So, we have done our practice. Now, go to on SSMS. Here, we need to truncate our table. So, as we can see, we do not have any data in this table. So, I am going to execute this practice. So, practice has been executed successfully. Now, we can stop this practice. Go to on SSMS and we can execute our selected statement. As we can see, 19,932 rows has been inserted successfully in our table. So, this is our case two. So, I hope you have understand how we can use the merge transformation with sort transformation and without using sort transformation. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. Thank you so much.