Creating multiple simultaneous Lookups to the same table that use Full cache.
Whilst developing a DTSX package for my ETL process I came across an issue that had me stumped for a while. If you have multiple Lookup Data Flow items in your packge that perform lookups to the same table then you may encounter a situation where the second lookup to a table generates an error in the package.
In the package (above) my calls to to the Lookup table (Segment 2(2) & Segment 3(2)) are made simultaneously and using identical code, however the second request fails with the following error;
A quick bit of research indicated that caching may have been the issue here – https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4010917&SiteID=17 – but I have found an alternative solution to the problem that allows you to keep your caching set to Full. If you change your Lookup Data Flow item such that the lookup columns are defined in a SQL Query and you include a unique alias for the tablename, you can then lookup values in the same table multiple times in the same package using Full cache mode.
GP23 Consulting 