We are developing packages in a SSIS environment using Version 9.00.3042.00. I’m presently having problems or a misunderstanding on when to use Partial or Full cache on lookups. Seems like the lookup results differ when using these two modes. For example, full lookups often “succeed” (turn green) but result in no matches while changing the same lookup to use partial or no cache will result in data matching but much slower performance. This has been observed on tables of varying sizes, but is most common on large (hundreds of thousands of rows) tables. I would like to know if anyone is experiencing similar issues and if possible to offer some explanation and possible workaround or corrective measures. I would also be interested in knowing if there any other methods to speed up lookup performance. Would like to find out what the best practice for writing SSIS lookups. Any help is much appreciated.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!