auto restart of SSIS package?

Question

i have been charged to research the possibility of restarting an SSIS package after it has come across as error. i understand that if you use checkpoints that you still have to manually restart the package. i was thinking of having the package kick out the bad records to a flat file and then the package would restart on its own.  so is there a way for a package to restart on its own with? i would like to know some ideas that can point me in a direction.
thank you

Answer 1

I think this can be done in multiple ways. 

1. you can use a parent package which can performs some action upon failure of a child package
2. You can query the system metadata tables to findout the status of the package or job and then invoke relevant SPs to restart the package.

I haven't tried this but I am sure that this is not so difficult to achieve.

Answer 2

Scheduling Package Execution in SQL Server Agent

 

You can automate the execution of SQL Server Integration Services packages by using SQL Server Agent. SQL Server Agent is the Windows service that lets you automate tasks by running SQL Server Agent jobs. SQL Server Agent must be active before local or multiserver jobs can run automatically. For more information, see Configuring SQL Server Agent.

To automate package execution, you create a job using the New Job dialog box that SQL Server Management Studio provides for SQL Server. For more information, see Implementing Jobs.

After you create the job, you must add at least one step and set the type of the step to SQL Server Integration Services Package. A job can include multiple steps, each running a different package. A SQL Server Agent job step can run Integration Services packages that are saved to the msdb database or to the file system. For more information, see Creating Job Steps.

Link:
http://msdn.microsoft.com/en-us/library/ms141701.aspx


However you can ask more in SSIS forum.
Regards,
Jai