We all are aware how beneficial lookup tasks are within SQL Server Integration Services. They are simple to use and also perform very well.
However, for complex lookups you cannot use the simple lookup tasks. For this you need to use range lookups. To learn how to, follow this link : http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range .
Although it does handle really complex scenarios and avoids using complex correlation joins etc in the sql, the performance can be significantly slower. I had a package that processed around 30 millions rows in 3 hours...now takes around 12 hours to do the same number of rows. This is because to enable range lookup you have to turn on the memory restriction. This basically means SSIS will only pull back one row at a time. Which is actually what you want but in terms of a large dimension (at a guess a couple of million) this could pose an issue. So in general as long as you index your table properly it shouldn’t be too much of an issue however it can never be as quick as 'cached lookup' which is the standard lookup.