Meagan Longoria shows us how to use a variable to populate a lookup query in SSIS:
I already had my data flow populated with the lookup for MSA. I set it to full cache and entered a query in the connection to initially populate the fields that would be returned (simply my lookup query without the where clause).
Next, I opened the data flow properties, located Expressions and clicked on the ellipses to open the Property Expression Editor. I found the SQLCommand property for my MSA lookup and set it to my package variable that contained my query.
I had issues in the past with full cached lookups and variables. Fortunately, you can get around a lot of problems with expressions.