![\"\"](\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2020\/04\/Merge-Join-1.png\")
<\/a><\/figure>\r\n<\/div>\r\n\r\n\r\n\r\nI also added some dummy data as you see below. I\u2019ve highlighted what is different and will need to be changed in the destination table.<\/p>\r\n\r\n\r\n\r\n Let\u2019s open SSIS and create a new SSIS project. Next, drag and drop two OLE DB source components into the design window. I\u2019m going to rename mine Source and Destination to match my table names.<\/p>\r\n\r\n\r\n\r\n Configure the two OLE DB sources to match the source table and the destination table. Below is a screenshot of my source connection manager.<\/p>\r\n\r\n\r\n\r\n Once your connection managers are configured let\u2019s drag and drop two Sort components below each OLE DB Source and connect them to each source. We will be using a merge join next and the merge join component needs to have data sorted in ASC or DESC order. I always choose to sort on the Primary Key for each table.<\/p>\r\n\r\n\r\n\r\n In my example, I\u2019ll choose to sort on my primary key, ID.<\/p>\r\n\r\n\r\n\r\n Next, drag the Merge Join transformation into the Design window and drag the data path from the Sort component to the Merge Join. When you attach the arrow to the transformation, the Input Output Selection dialog box appears, displaying two options: the Output drop-down list and the Input drop-down list. The Output drop-down list defaults to Source Output, which is what we want. From the Input drop-down list, select Merge Join Left Input, as shown below. We\u2019ll use the other option, Merge Join Right Input, for the other connection.<\/p>\r\n\r\n\r\n\r\n Next, connect the data path from the other Sort component to the Merge Join transformation. This time, the Input Output Selection dialog box does not appear. Instead, the Input drop-down list defaults to the only remaining option: Merge Join Right Input.<\/p>\r\n\r\n\r\n\r\n Now, let\u2019s configure the Merge Join transformation.<\/p>\r\n\r\n\r\n\r\n The first setting in the Merge Join Transformation Editor is the Join type drop-down list. From this list, you can select one of the following three join types:<\/p>\r\n\r\n\r\n\r\n For our example, we want to include all rows from left table but only rows from the right table if there\u2019s a match, so we\u2019ll use the Left outer join option.<\/p>\r\n\r\n\r\n\r\n You now need to select which columns you want to include in the data set that will be outputted by the Merge Join transformation. For this exercise, we\u2019ll include all columns. To include a column in the final result set, simply select the check box next to the column name in either data source.<\/p>\r\n\r\n\r\n\r\n Almost finished, but first let\u2019s add a Conditional Split transformation. This will allow us to insert new records or update previous records.<\/p>\r\n\r\n\r\n\r\n In the Conditional Split Editor, I created two outputs. If (Destination) ID is NULL then the record doesn\u2019t exist so we\u2019ll perform an INSERT. If the ModifiedDate is different between the two tables then we know something has been updated since the last execution and we need to update the record. See below.<\/p>\r\n\r\n\r\n\r\n Since we can perform and INSERT or an UPDATE we\u2019ll need two destinations. First, for the INSERT, we\u2019ll simply be doing an INSERT into the table so we can drag the OLE DB Destination component into the window, choose \u201cINSERT\u201d in the Input Output selection window, and use the Reporting connection manager.<\/p>\r\n\r\n\r\n\r\n I\u2019m going to check the \u201cKeep Identity\u201d box since the ID column is an identity column.<\/p>\r\n\r\n\r\n\r\n Next, for the UPDATE statement we\u2019ll drag the OLE Command component into the window and configure it. Select \u201cUPDATE\u201d in the Input Output Selection window.<\/p>\r\n\r\n\r\n\r\n In the Connection Managers tab, assign the connection manager for Reporting.<\/p>\r\n\r\n\r\n\r\n In the Connection Managers tab, assign the connection manager for Reporting.<\/p>\r\n\r\n\r\n\r\n In the column mappings tab, assign parameters:<\/p>\r\n\r\n\r\n\r\n Final package should look like the following:<\/p>\r\n\r\n\r\n\r\n Save and execute. You can see that we updated two records and inserted one record:<\/p>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n
Drag and drop a Data Flow task into the design window, right click, Edit:<\/p>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n\r\n
<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n<\/a><\/figure>\r\n\r\n\r\n\r\n