SSIS Merge Join for Incremental Load .

We can use Merge Join transaction to achieve incremental loading of data:

Add a Data Flow task containing the following process:

Step 1: Add two OLEDBSource configured to Customer table in Source DB and DimCustomer table in Target DB.

Step 2: Add two Sort transaction (see Image 1 below) and sort data by Customer ID.

Step 3: Add a Merge Join, and config as shown below:

Step 4: Add a Conditional Split and create a condition to get CustomerID with Null values.

Step 5: Now redirect rows containing CustomerID with Null values to OLEDB Insert DimCustomer and other records to OLEDB Update DimCustomer (OLEDB Command).

Step 6: In OLEDB Command add the below query and in SQL Command and perform the mapping as shown in images below:

UPDATE [Demo].[dbo].[DimCustomer]
   SET [CustomerName] = ?
      ,[MaritalStatus] = ?
      ,[Gender] = ?
      ,[AddressLine1] = ?
      ,[AddressLine2] = ?
      ,[Phone] = ?
 WHERE [CustomerID] = ?

Now execute the package to perform incremental load.