how to use parameters inside look up transformation in ssis

Question

I have an input csv file with columns eid,ename,designation. Next i use Lookup transformation, inside look up am using query like

select * from employee where ename=?  

i need to pass parameter ? from csv file. That is ename which is in csv file has to be passed into the query using Lookup transformation.

Inside Lookup i have changed mode to Partial cache, and inside Advanced tab, i selected Modify the SQL Statement and placed my query, and clicke on paramters tab. But i don't know like how to pass the parameter.

Answer 1

you cant add parameters to your lookup query. If by adding the parameters your goal is to reduce the amount of data read from the database, you don't have to worry, the "partial cache" will do that for you.

Partial cache means that the lookup query is not executed on the validation phase (like the full cache option) and that rows are being added to the cache as they are being queried from the database one by one. So, if you have one million rows on your lookup cache and your query only have reference to 10 of those rows, your lookup will do 10 selects to your database and end up with 10 rows only.