Welcome to EMC Consulting Blogs

One caveat with using this technique - or any that require you to enable memory restriction - SSIS will execute a seperate query for each lookup value not found in the cache, and the cache starts empty. If memory restriction is not enabled, the entire table or query is cached in memory as the first step of the lookup, then each row can be matched without executing another query.
If this is a small table, or most of your input rows have the same lookup value, it may not be much of a hit. But if you have a large number of unique lookup values in your input, it can really impact performance.
Another workaround for this is to do the lookup as a join in your Data Source query. It can cause issues if your lookup table is on a different server than your source, but  that can be worked around with temp tables.

I covered Range lookups a while back, as John mentioned modifying the SQL in the Lookup is bad for performance. My post includes some code for a custom transform to do a range lookup.