Parameters for Lookup Transformation Query!

Hi Gurus,

I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.

In the 'Use Results of an SQL Query' pane of Lookup Transform:

Code Snippet

EXEC GetMonthlyDataExtract 4, 2007

( I am passing month and year values) this works ok.

But when I chage to

Code Snippet

EXEC GetMonthlyDataExtract ?, ?

It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.

Also I am interested to map parameters to variables not to input columns.

If mention if that is not possible or any other alternative.

Your help will be appreciated.

Thanks,

Paraclete

It's a bit of a workaround, but I think you could put your variables in data columns and then use the Lookup in partially cached mode. Go over to the Advanced tab, check Enable Memory Restriction, Enable Caching, and Modify SQL Statement. Put your parameterized EXEC in the statment and set the parameters based on your variable/columns. Instead of executing your procedure in the PreExecute phase, now it will get executed on the first row. Since you're caching the results, the procedure should never get executed again. So it's effectively the same functionality.

Update:
On second thought, the Lookup workaround described here probably won't work. When the Lookup is in partial cache mode, it doesn't perform its own matching, but relies on the modified query and the database engine to perform the matching. Since the parameters to your procedure are not the columns you want to match on, the Lookup won't return the correct matching row.

You may also consider using the Merge Join instead of Lookup.