Using lookup expressions

To take advantage of the full cache mode in Lookup transformations, but only retrieve a subset of reference rows, you can supply the lookup query at run time. Certain properties of the data flow task can be modified at run time using expressions; the query used in a Lookup transformation is one such property.

In this recipe, you are going to dynamically determine the query restrictions and prepare the reference query in each iteration of the Foreach loop container processing the input files.

How to do it...

1. Make sure that the AdventureWorksETL.sln solution is open in SSDT, and that the LookupExpression.dtsx package is active in the control flow designer. The solution is located in the C:\SSIS2016Cookbook\Chapter06\Starter\AdventureWorksETL\ folder.
2. Create three new package variables using the following information; the Variables window can be opened by selecting Variables in the SSIS menu when an SSIS package is active in the control flow designer:

Name	Data type...