September 12, 2010 at 4:04 pm
I have a for each loop that is looping through some files. For each file it determines the invoice period the file is for (hardcoded in the file name), deletes the old lookup cache, and recreates it with the data that is active for the invoice period.
The first file runs ok. But the second file fails when it tries to load the cache again, even though the step before deleted the cache.
Here is the error message: [CACHE_tbDirectIdentificationRule [22]] Error: The component "CACHE_tbDirectIdentificationRule" (22) cannot write to the cache because component "CACHE_tbDirectIdentificationRule" (22) has already written to it.
I was hoping to use a cached lookup to get around not being able to pass in variables to a regular lookup and still have it run fast. I am loading files with about 4 million rows apiece and found partial caching too slow.
Is there some command I could put in a script task, perhaps, that would free up the cache so it can be rebuilt? Apparently just deleting the cache file does not do the job.
If I restart the job from the beginning I get the same results. The first file runs successfully and the second file fails with the above error.
This is the query I use to load the cache:
selectcarrierHQCd,
CMSPartDTypeCdIsNULLInd
fromtbDirectIdentificationRule
wheredirectIdentificationRuleIsActiveInd = 'Y'
anddirectIdentificationRuleStartDt <= ? /* stop dt */
and(directIdentificationRuleStopDt is null
or directIdentificationRuleStopDt >= ?) /* start dt */
The variables refer to the start and stop dates of the invoice period.
September 12, 2010 at 10:03 pm
I'm afraid that you can't force SSIS to release the cache file. It opens the connection, then maintains it (like any other Connection Manager) until the package completes execution.
You may be able to get around this by exploiting parent/child packages. Parent/child packages don't share connection managers - therefore, if you had a parent package coordinating your execution it could contain the foreach loop. Inside the loop, you could call a child package to update the cache file, and a separate execute package task to use the cache file.
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
September 13, 2010 at 7:42 am
Thank you! That gives me ideas on how to work around this.
June 4, 2012 at 11:22 am
Hope you can help. I have a For Each Loop transform with an Execute Package where the child creates Caches for each station I want to process. Problem I am having is the second iteration through the package will just stop when a certain cache transform is in the process. All caches are stored in memory
1) Create Current Station variable list
2) For Each Loop processes for first station
3) Execute Package kickes off
4) First Data Flow Task creates 4 Caches with Cache Transforms
5) Seconf Data Flow Task executes and processes records based on those caches
6) Package Completes
7) For Each Loop begins process for second station (I checked this through a scriptask that displays the Station variable and the query variables derived from the station.
8) First Data Flow task starts------and then stops. No error messages, just stops executing.
If I remove one of the 4 cache transforms, the process works. If I change that 4th one to use a RAW file, it sill doesn't work, so it does not appear to be memory.
This is SQL 2008 R2
June 4, 2012 at 12:51 pm
Are these file caches, or memory caches?
From what I recall, you won't be able to make them work unless they're file caches...
What error messages, etc. do you get when "it doesn't work"?
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
June 4, 2012 at 2:32 pm
Actually found it was a server error. These were memory caches, and it does work. I was able to run down the error in one of my source servers. Thanks for responding
October 21, 2019 at 11:36 am
Seeing a similar issue with Loading a Cache Connection Manager via a Cache Transform George. Was your source system error a data driven error or a source system server (e.g. Memory) error. I appreciate this was a long way back so if it has gone from your notes then no problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply