Using Lookup Transformations with SSIS (Part 2/2) – Advanced

In Microsoft SQL Server Integration Services 2012 (SSIS) Lookup Transformations can be used to integrate external data into your Data Flow. In my previous post, we discussed how you can leverage the different cache modes available to optimize the performance of your Lookup Transformation. One of the cache modes we discussed was Partial Cache. When partial cache is selected, you are given access to the “Advanced” tab of the Lookup Editor. In this entry, we will focus on the advanced functionality of the Lookup Transformation.

The previous entry discussed the different parameters of partial caching. The advanced tab allows you to set the size of your lookup cache for execution in both 32 and 64 bit modes. It also allows you to specify cache for rows with no matching entries.

The most interesting part of the advanced tab, however, is the ability to customize your lookup query. In the columns section of the Lookup Transformation, you supply the criteria for the lookup. In most cases, you are performing your lookup based on matching values. In some cases though, you may want to perform a lookup matching a range of values or vary your query based on the value of a variable or another column in your data flow.

In order to do this, click the “Modify the SQL statement” checkbox and make the necessary modifications. Just like in the source editor, you can use the “?” symbol to indicate the use of a parameter. Furthermore, you can also modify your Lookup Transformation to vary the query by placing an expression for the “SqlCommand” property on your Data Flow Task.

A word of caution when modifying the query in a Lookup Transformation: if your query is especially complex, you may have issues with performance. This is because each row entering the transformation triggers a query to be issued against a database somewhere. When your lookup query is complex, this can put a high level of stress on the database that your lookup is being performed against. In this case, performance boosts can often be achieved simply by adding indexes. However, sometimes this cannot be done effectively. Make sure you test your Lookup Transformation for performance. If you cannot tune your lookup to an acceptable level, you may want to consider placing all of your data in an intermediate table and using the SQL Database Engine to perform your lookup in a source query.