Using a parameterized query in a lookup can lighten the cached data, especially in the case of iterative processing, and you do not have to load all the data into the cache.
Even though the lookup component does not offer the ability to use variables but it does have the property SqlCommand which allows to specify the query of the lookup and that can be parameterized.
Take the example of a lookup that loads the client list against a specific client type stored in a project parameter, for this we modify the expression in the property SqlCommand exposed at the data flow (DFT) level, as shown above:
We have a first window where we can set our lookup with the property SqlCommand. Then, click on the three points.
A new window opens to allow us to create the parameterized query.
Since the expression editor expects text, we are forced to convert our parameter to a string.