When working with SQL Server, developers often come across situations where they need to perform complex joins on their data. While standard joins are commonly used, there are also “exotic joins” that allow for more specific criteria to be applied during the join process.

So, what exactly is an exotic join? An exotic join is when additional criteria are applied to the join, such as using a select statement, AND’s, OR’s, or BETWEEN statements. These joins are not as commonly used as standard joins, but they can be very useful in certain scenarios.

Let’s take a look at an example of an exotic join using the BETWEEN statement:

SELECT *
FROM TableA a
INNER JOIN TableB b ON (a.ValueA BETWEEN b.LowRangeLimit AND b.HighRangeLimit)

In this example, we are performing a range lookup by joining TableA with TableB based on the condition that the value in TableA.ValueA falls between the LowRangeLimit and HighRangeLimit in TableB.

Now, let’s see how we can translate this exotic join into an SSIS package. SSIS (SQL Server Integration Services) is a powerful tool for data integration and transformation. To perform a range lookup in SSIS, we can use the Lookup component.

Here are the steps to create a range lookup in SSIS:

1. Create a Lookup component in your SSIS package.
2. Select the lookup table in the reference table tab. This can be done using your OLE DB Connection or a SQL statement.
3. In the columns tab, map the field that needs to be checked against the lower range value field. The columns must be of the same data type.
4. In the advanced tab, enable the “Enable Memory restriction” option and check the “Modify SQL statement” checkbox. This will allow us to add a range lookup in the query using <= or >=.
5. Click on the Parameter button and assign the parameter to a field. All the parameters (indicated by ?) should be assigned the same field name.

Here is an example of the SQL statement used in the Lookup component:

SELECT *
FROM (
  SELECT LowRangeLimit, HighRangeLimit, LookupValue
  FROM LookupTable
) AS refTable
WHERE ([refTable].[LowRangeLimit] = ? OR [refTable].[LowRangeLimit] < ?)
  AND ([refTable].[HighRangeLimit] = ? OR [refTable].[HighRangeLimit] > ?)

By following these steps, we can implement a range lookup in our SSIS package. This can significantly improve the performance of our data processing, as demonstrated in a real-world example. In the example, the processing time was reduced from 23 minutes to 13 minutes in the production environment.

It’s important to note that there are other ways to implement a range lookup in SSIS, but the method described here is the simplest and most straightforward. If there is enough interest, I will be happy to explain the reverse process in a future article.

In conclusion, exotic joins in SQL Server, such as range lookups, can be a powerful tool for data analysis and processing. By understanding how to translate these joins into SSIS packages, developers can optimize their data integration workflows and improve overall performance.

Thank you for reading!

Author: [Your Name]