SSIS Development...Cache and Lookup Transformation vs. Index/Materialized Views

I'm in the process of creating and testing new templates for my company's SSIS packages. A colleague and I are debating/wondering about Cache and Lookup Transformation vs. Index Views (materialized views). These packages will be used for incremental loads....

with these in mind, does anyone have any guidelines, suggestions or things I should keep in mind as we determine the more optimized route?

Overall we're looking to reduce current package execution times. I personally don't see the use of materialized views b/c of the required data maintenance, restrictions in script, etc.

thanks for the reply....

for arguments sake, let's say the lookup table is betwwen 100K and 300K rows

Go with the lookup in full cache mode. 300k is nothing (make sure you only use the columns you need though) a server can't handle.

Are you looking up against (pulling) most of those 100-300K records or just small portion of it?

It's my understanding that it's a good idea to use full cache when you are accessing "large" amount of data from a "small" reference table. Caching happens before the main parts of pkg start executing. Depending on your environment and the amount of memory/lookup record set, it might or might not slow down the server (might not be an issue if this processing happens during off hours). SSIS pros, please correct me if I am wrong. But like Koen said, if your data fits into the memory, go with lookup as it doesn't require any maintenance like the other option.

Maybe you want to to test both scenarios in a test pkg (using sql profiler)?