This is a very unique problem that I have run into. Let me start by giving you the details:
We have parent package(lets call it A) designed for processing cubes both partial and full processing. Now this package calls two other packages (say B, C) one to load staging data and one to process the cubes.
A few weeks ago, we started to have a connection issue due to which the processing of all cubes failed. This happens like once or twice a week since then. Apparently the SQL server fails to establish a connection( only for a few seconds). But this loss of connection fails all the remaining cube processing.
Now the only way to fix this issue is to restart the parent package all over again. we run a script to update the flags on which cubes have already been processed before restarting the entire package. This helps us skip the cubes which have already processed successfully.
So i have been trying to design this child package (responsible for cube processing) in such a way that if a cube fails, it is automatically sent for reprocessing. Following is the logic that I used:
In this what I did is that I put this process cube task in a for loop container and set the counter to 2. If the cube fail count is less than 2, it will try to reprocess and if the fail count is greater than 2, it exits the loop and fails the processing of that cube. By limiting the retries by 2 we avoid the risk of an endless loop which can happen if there is a data issue and the cubes will fail every single time.
But this does not fix my issue because the cube has already acquired a thread and will reprocess is the same thread and not in a new one. And in order to fix the connection issue, it has to acquire a new thread.
Now coming to my question,
How can i design this package in such a way that if a cube fails, it triggers reprocess the cube in a new thread or better still, stops the package, runs the parent package again after running the script to update flags and reprocesses the cube. Is that even possible? or is there a better way to tackle this isse?
I have never faced this issue before so I am a little stuck on how to proceed. So any help would be greatly appreciated.
