sql server - SSIS package to automate the reprocessing -

this unique problem have run into. let me start giving details:

we have parent package(lets call a) designed processing cubes both partial , full processing. package calls 2 other packages (say b, c) 1 load staging data , 1 process cubes.

a few weeks ago, started have connection issue due processing of cubes failed. happens once or twice week since then. apparently sql server fails establish connection( few seconds). loss of connection fails remaining cube processing.

now way fix issue restart parent package on again. run script update flags on cubes have been processed before restarting entire package. helps skip cubes have processed successfully.

so have been trying design child package (responsible cube processing) in such way if cube fails, automatically sent reprocessing. following logic used:

in did put process cube task in loop container , set counter 2. if cube fail count less 2, try reprocess , if fail count greater 2, exits loop , fails processing of cube. limiting retries 2 avoid risk of endless loop can happen if there data issue , cubes fail every single time.

but not fix issue because cube has acquired thread , reprocess same thread , not in new one. , in order fix connection issue, has acquire new thread.

now coming question,

how can design package in such way if cube fails, triggers reprocess cube in new thread or better still, stops package, runs parent package again after running script update flags , reprocesses cube. possible? or there better way tackle isse?

i have never faced issue before little stuck on how proceed. appreciated.

i think adding checkpoints package well. detailed put in answer lets start again point of failure try link

https://www.simple-talk.com/sql/sql-tools/implementing-checkpoints-in-an-ssis-package/