August 28, 2018 at 6:39 am
Hello Friends,
I have a parent package which calls 10 child packages and is running in the production server. Due to memory issues or foreign key violation issues , the package gets failed , not every time but most of the time. I will have to resolve the issue manually and restart the job in the server. I am trying to automate the process of creating one more job and a package.
My idea of implementing is
Calling the parent package and if it fails , it should go the next task (execute SQL task) which will take care of the memory and Foreign key violation issue. then restart the package from the point of failure.
In here, I am not able to restart the package from the point of failure (using checkpoints).
Please provide me your suggestions.
Thanks,
Charmer
August 28, 2018 at 6:48 am
Charmer - Tuesday, August 28, 2018 6:39 AMHello Friends,I have a parent package which calls 10 child packages and is running in the production server. Due to memory issues or foreign key violation issues , the package gets failed , not every time but most of the time. I will have to resolve the issue manually and restart the job in the server. I am trying to automate the process of creating one more job and a package.
My idea of implementing is
Calling the parent package and if it fails , it should go the next task (execute SQL task) which will take care of the memory and Foreign key violation issue. then restart the package from the point of failure.
In here, I am not able to restart the package from the point of failure (using checkpoints).
Please provide me your suggestions.
One idea is to create a queueing system.
When the job starts, it write a queue to a SQL table (a list of packages to be executed for the job).
As the packages complete, they update a flag in the queue (IsCompleted BIT, or similar).
When you run the master package, you run it in 'Ordinary' or 'Restart' mode.
If it's in Restart mode, it finds the most recent queue (instead of creating a new one) and starts executing from the first package in the queue where IsCompleted <> 1.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 28, 2018 at 7:19 am
So I guess when child package completes, it updates the queue table using event handler and also we will have to re-run the master package manually?
Thanks,
Charmer
August 28, 2018 at 7:50 am
Charmer - Tuesday, August 28, 2018 7:19 AMSo I guess when child package completes, it updates the queue table using event handler and also we will have to re-run the master package manually?
How you update the queue table is up to you. Could be as simple as an ExecuteSQL task in the master package (depending on how your master package is structured).
I had not realised that you wanted the restart to happen automatically. Isn't this the same as gracefully handling the errors instead of failing the entire job?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 28, 2018 at 9:17 am
It should have been handled gracefully but instead the job is designed in such a way. It simply calls the master package and if any of the child package gets error , it stops and we will have to check the log and fix the issue and re-run the job again. I am trying to modify it in a better way. So pleas provide me your suggestions. I am all ears 🙂
Thanks,
Charmer
August 29, 2018 at 5:19 am
Charmer - Tuesday, August 28, 2018 9:17 AMIt should have been handled gracefully but instead the job is designed in such a way. It simply calls the master package and if any of the child package gets error , it stops and we will have to check the log and fix the issue and re-run the job again. I am trying to modify it in a better way. So pleas provide me your suggestions. I am all ears 🙂
There is no 'one size fits all' response to your question, but the general idea would be to
a) Not fail the master package if child packages fail
b) Use event handlers in the child packages to handle errors – to ensure that the errors are logged, at a minimum
c) Consume any errors from within the master package and act accordingly.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply