In this article, we are going to perform SSIS Left Outer Join on two SQL tables using Merge Join Transformation in SSIS. Before we start creating the SSIS Package, let us see the two source tables that we use for Left Outer Join in SSIS using Merge Join transformation.
Employees Table inside the [SSIS Tutorial] Database is:
Department Table inside the [SSIS Tutorial] Database is:
SSIS Left Outer Join using Merge Join Transformation Example
STEP 1: Drag and drop the data flow task from the the toolbox to the control flow region. And rename it as Performing Left Outer Join in SSIS Using Merge Join Transformation
Double click on the data flow task will take us to Data flow region.
STEP 2: Drag two OLE DB Sources to the data flow region. And then, double-click on the first OLE DB source to open the connection manager settings. It also gives an option to choose the table containing the source data.
From the above screenshot, you can observe that We selected [Employees] Table from [SSIS Tutorials] Database
STEP 3: Click on the columns tab to check the columns. In this tab, we can uncheck the unwanted columns.
STEP 4: Double click on the 2nd OLE DB source to configure the Second table to perform Left Outer Join in SSIS. Here, we are selecting the [Department] from [SSIS Tutorials] Database
STEP 5: Repeat step 3
STEP 6: Drag two Sort Transformations from the toolbox to the Data Flow region. And connect the OLE DB Source output arrows to this Transformation.
STEP 7: Please configure the Sort Transformation. Check the columns we want to sort and Pass Through the remaining column. If you forget to select the Pass Through, those columns won’t appear in output columns.
From the source data, DepartID is the Joining key between Employees and Department tables. So we are sorting the Employees table by DepartID in ascending order.
TIP: The Merge Join Transformation won’t work without sorting the input rows. So Sort Transformation is mandatory before applying Left Outer Join in SSIS.
Click on Sort Transformation1 and sort the Department table using the id column in ascending order.
STEP 8: Now drag and drop the Merge Join Transformation into the data flow region to perform Left Outer Join on the sorted tables.
Configure Left Outer Join in SSIS Merge Join
When you pull the output arrow of the Sort Transformation into Merge Join Transformation, an Input Output Selection window appeared to choose whether it is Merge Join Left Input or Merge Join Right Input.
Here we selected Merge Join Left Input for Employees table. Click ok and drag and drop the output arrow of the Sort Transformation 1. Here Merge Join Right Input is automatically selected by the intelligence.
STEP 9: Double click on Merge Join Transformation will open the Merge Join Transformation Editor to configure it.
Join Type provides a drop-down list to select the Join type (such as Inner Join, Left Outer Join, and Full Outer Join in SSIS) you want to perform on the source data. In this example, we are performing Left Outer Join. That’s why we selected the SSIS Left Outer Jon option from the Join Type option.
As you can observe from the source data, both Employees and Department tables are joined using DepartID and id columns. So select them as Join keys by dragging the arrow.
Now decide the columns you want to transfer to the target table (Pass through columns) by check-marking the column names.
From the above screenshot, you can observe that, We selected [FirstName], [LastName], and [Department] columns as output columns.
It means Left Outer Join in SSIS will produce all the [First Name], [Last Name] column rows from the Employees tables, and matching rows of [Department Name] column where DepartID is exactly equal to any of the id column data. All the non-matching rows of the [Department Name] column treated as NULL.
STEP 10: Drag and drop the OLE DB destination into the data flow region. Next, double-click on it to configure the destination server and destination table to store the Merge Join Transformation output
Here we selected [SSIS Tutorials] as database and [Left Outer Join Using Merge Join Transformation] as the destination table
STEP 11: Click on the columns Tab to check whether input columns are matching with the destination columns or not. If not, Please assign the appropriate columns to destination columns
We finished designing the Left Outer Join in SSIS Merge Join package. Let us execute the package
Now let’s open the SQL Server Management Studio and select the columns from [Left Outer Join Using Merge Join Transformation] table.