October 6, 2014 at 4:21 am
Good Morning,
I'm hoping someone can help me with this.
I have a SSIS package that basically loops through a number of child packages based on whether they can restart or not. If they can restart then the master package will re-run the failed child package and carry on with a predetermined list in the event of any failure should a particular child package permit a restart.
Without going into the nitty gritty details of how this package works, I need to basically fail the whole master package (not an issue) in the event of any of the child packages fail, but then I need the master package to automatically start over again from the beginning. The way the package works it needs to start from the beginning as it gathers a run number and logs to a custom event log I have implemented.
If I use checkpoints my understanding is this will try to restart from the last failed object which would be the For Each Loop container and not the first task which is to generate the run number.
The master and child package combination run great as designed if I manually click the restart button in debug but I need some mechanism to automate this for me.
Thank You
Andy
October 6, 2014 at 4:28 am
Which version of SSIS are you using?
In SQL Server 2012 and above, you can start packages using stored procedures.
Maybe you can implement some restart machinism there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 6, 2014 at 4:31 am
Hi thanks for your reply,
I'm using SQL Server 2012, Visual Studio 2010. I'm looking at any option available. How would I code a stored procedures to perform a restart?
Sorry for my lack of expertise in this, I'm relatively new to writing complex SSIS packages.
October 6, 2014 at 4:40 am
You can execute a package stored in the SSIS catalog using stored procedures:
Deploy and Execute SSIS Packages using Stored Procedures
You can execute the package synchronously using a parameter:
Execute a SSIS package in Sync or Async mode from SQL Server 2012
So maybe you could put all this in a WHILE loop and keep executing the master package unless a condition is met.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply