Problems with LOOKUP component (repost)

Hello,
Can anyone address these:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142849&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142837&SiteID=1

-JamieAnyone?

Where's Matt David when you need him :)

-Jamie|||Jamie,
It does make sense that you can't override the sql statement for a lookup component since you would typically connect to a relatively static table to import in a related column. My example would be to pull in a State's full name when the source file only has a State code (NY --> New York).
What are the circumstances for which you need to override the sql statement?

You could try using a stored procedure as the SQL command and passing in your variables.
exec SPName [User::Variable], [User::Variable]
From what I've tested this only works when the stored procedure is expecting nvarchars and the variables are strings. Any other types cause conversion errors.
Larry|||Do you know what Larry...I honestly can't remember. Embarrassing huh?

I am making an educated guess that it was something to do with limiting the the amount of data that you cache. In some situations you will know that the data in your pipeline could only be in a subset of the target, so what's the point in pulling back the whole of the target?

For example - you are using a LOOKUP to determine whether the incoming rows are new or changed rows. You know that rows in the source can only be updated within 2 weeks of being created (this is a real world scenario that I have come across) so you only want to pull back stuff that was created in the past 2 weeks. Admittedly in this scenario you could use GETDATE() but it demonstrates how the data required in the cache may only be a subset of the total data and that subset will change over time.

Another scenario would be where the data-flow is in a for each loop so it can pull data from numerous sources. The target has a field indicating which source each row has come from so in your LOOKUP you only need to cache data from the source that is currently being processed.

Make sense?

-Jamie|||I can't think of the exact scenario right now, but I know I have wanted to do the same thing. I want to restrict the reference data to a subset, but the filter clause can change, and you would want to parameterise it, usefull feature in my book.|||Jamie,
I do remember other posts regarding that type of scenario. I just looked at the Lookup transformation again. Couldn't that be accomplished by using the Modify SQL Statement under the Advanced tab and mapping your lookup parameters correctly?
For example if your source data looks like as follows
Col A Col B Col C
1 A X
2 A Y
3 B Z
And your lookup for Col B looks like as follows
Col B Description
A A's Description
B B's Description
...
Z Z's Description
You could map the lookup parameter to the source's Col B. I'm not sure about the performance impact on the lookup column. It might do a group by on the source to get the unique parameters, or it might do a value by value lookup if it's not already in the cache.
The only downside is that the lookup subset parameters need to have corresponding values in the source.
The other mechanism for large lookups is to use a Merge Join -- this is outlined in one of the Project Real documents.
Larry
|||Larry,
Good point though even if parameterised queries could do the job I would still want to be able to use an expression. I seldom, if ever, use parameterised queries because expressions do the same job much much better.

-Jamie|||Jamie,
To me both your suggestions for Lookup(add AlwaysUseDefaultCodePage and make SqlCommand expressionable) are sensible and worth reconsideration -although to accomplish these, further details have to be carefully discussed. How about you raise them as DCRs so our triage can review and decide how to take care of both?

Thanks
Wenyang|||Thanks Wenyang,
I've submitted it via the product feedback centre.

People, if you want to vote for this to be done then go here: http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=5ee6da3e-4d8b-4ada-a526-1034c11d6763

-Jamie