SSIS: MERGE JOIN FOR INSERT AND UPDATE (UPSERT)

Today I would like to present a SSIS package that will handle the insert and update task within the target table.

Let's start from the beginning...
First of all we can go right to the Data Flow tab.

We will need 2 OLE DB Source items from the SSIS Toolbox. One will be used to capture the source table, one for the destination table. In a second step, we will need to sort the primary key before we join the 2 tables.
The first steps can actually be replaced by an OLE DB Command item. Actually later is even recommeded since the Sort item is quite expensive. Since I have only a very small dataset and due to demonstration reasons, I will go for this option now.
Next item we need is Merge Join. Since I know the full set is captured in source and the destination table is missing the delta load, I will select Left Outer Join here. If it's vice versa, you will need to go for an Full Outer Join.
In the next step, we need a Conditional Split item from the toolbox. Here we will distinguish between records that are missing in the target table, and records that have been updated in source, but not yet in the target table.

The new records can be simply loaded into the destination table by using the OLE DB Destination item.
For the updated rows, we will need a Stored Procedure that will map the new rows and update them in target.

In a final step we will use an OLE DB Command to call the procedure and handover the parameters.

Now we will have the full set in both tables. Please check the video above to get a more precise picture.