Hi,

Can anyone please tell me the best approach to run SSIS package (SQL Server 2008/2012/2014) automatically when it fails?

Many thanks,

Samuel

Hi Samuel

There are a few ways to do this, some of them are

• If you have a scheduled job then can add another step with the package that you want to run, then set the OnFailure to run that step.
• You can also use constraints with in you SSIS package work flow that will redirect to a task that you want to run in the event of a failure.   

Hi Samuel,

What is the point in doing that? It will fail again at same task for which it has failed earlier.

How to do it: I have never tried this but think adding an On Error event handler on the package and use an execute package task to call the same package. 

Do you mean that if one of the steps in the package fails you want the package continue running?

I'm assuming the OP wants to run a different package when one fails.

Either adjust the SQL Agent job, or create some error handling in your package, which results in the Failure package being called, in much the same way that you have (probably) created notification emails on errors.

Hi Samuel,

If you want to your SSIS packages to automatic restart and complete without you having to respond to the restart them after failure, we can takes advantage of SSIS behaviors with checkpoint files, SSIS features, and features of SQL Agent scheduling. For more details, please refer to the following documents:
Automatic Restart of SSIS packages after Failover or Failure
Restart Packages by Using Checkpoints

If you want to automatic run another package when package fails, we can refer MichaelAdrianJohnson and Patricks suggestions.

Thanks,
Katherine Xiong