Restartable or Rerunnable SSIS Packages

BI solutions need to be continually importing/updating data from external source systems. I believe that a good BI solution will have automatically correcting load and process jobs. What I mean by that is if a package fails (such as a power or system failure) the application will automatically correct itself next time it runs. In the old days, we used to create "restartable" jobs, where, after a failure, the operator would manually intervene to restart the job with specific parameters. I think there is little excuse nowadays to have applications that require manual intervention after a failure. They should be designed in such a way that they “know” where they are up to and continue, or reprocess.

For example, my Permon application (Perfmon Dashboard) has a job that runs every 180 seconds to transform and load the new records, and incrementally process the OLAP cube. Every now and again the machine is restarted for some reason and the Perfmon update process could be at any point when it is stopped. However, the job will automatically run from the right place, next time it runs. It will know whether there is 3 minutes or 10 minutes of data to process, by using high water marks in the database. In this way, I have no operational overhead in running this application. It just looks after itself. It is the same for the weblogs BI solution on Weblogs Dashboard. If your BI solution requires manual intervention after a failure or an error, get someone else in, like myself, to fix it up to run automatically without manual intervention.

Now having said that, it is often useful to be able to pass parameters to an SSIS package. (I just needed to mention automatically correcting jobs above, as some developers make packages restartable,to help with manual correction, when it should be automatic.) For example, you might want to have the ability to ask the SSIS package to conditionally run one task. This can be achieved quite easily by assigning the Disable property on the task (container etc) using Expressions on the task to set it to a parameter. To pass the parameter it is simply a matter of adding /SET "\Package.Variables[DisableBackup]";False to the cmd for the job in SQL Agent.