LookUp Transformation Dynamic SQL Query in ssis 2012

Question

I have been trying to add a dynamic query into LookUp Transformation editor(writing a query instead of a table for lookup) in SSIS 2012 and I am unable to do. I Have referenced many articles(here and this) and all have been saying to use SQLCommand in property and make it as expression. But I am unable to get that. Is there some thing missing? below is a screen shot where instead of expression editor I am getting String Value editor.

Since I don't have 10 reputation I am unable to post the Screen shot.

Answer 1

How about using Cache as source of your lookup? In that way you could use a dynamic sql statement in a DFT Source and use Cache Transform as your destination, then use that cache connection as source of your lookup.

Answer 2

In the Lookup Properties dialog box, make sure you have not selected "Full Cache" mode. Go to the Advanced screen, and check "Modify the SQL Statement". Here you can type in a SQL Statement with ? as parameter placeholders and click "Parameters..." to assign their values to input columns. Looks like you can't use variables, but you could populate an extra column in your dataflow source to populate the lookup parameter when it gets there.

Answer 3

The explanation from the answer you linked too was a bit confusing to me so maybe you had the same problem. What you have to do is this:

In your Control Flow select the Data Flow Task. In the Properties of the Data Flow Task you will see the property Expressions. Press the ellipsis and you should be able to configure the expression for the SQLCommand.