Simultaneous Lookups using Full Cache to the same table

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.
 
cc1
 
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;
 
 cc2
 
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.

 
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s