Figure 1 The basic structure of the demo "MainPackage" package - the package that will be automatically restarted after failover or server bounce.
· Set CheckPointFileName to the name of the checkpoint file you want this package to create when running
· Set CheckPointUsage to “IfExists.” This property is critical to make sure the package restarts only if it did not finish successfully before the server failure.
· Set SaveCheckpoints to “True”
1. A string variable to hold the name of the directory where the checkpoint file is to be stored.
2. A variable to hold the filename of the checkpoint file
3. A Boolean variable where the package will store whether the checkpoint file was found. This must have an initial value of “False.”
Figure 2 Sample variable set up for this "ScheduleAtStartup" package.
1. A “FOREACH” loop to go through all the files in the directory where the checkpoint file is to be stored.
2. A script task to compare the file names in the directory to the name of the checkpoint file that we have saved in the “CheckPointFileName” variable.
3. An “Execute Package” task that will be constrained so that it will only execute if the checkpoint file is found.
Figure 3 The overall structure of the control flow for the "ScheduleAtStartup" package.
· On the “Collections” tab, set the “Enumerator” to “Foreach File Enumerator”
· Below the “Enumerator, click the “Expressions.” Select “Directory” from the list of Expressions and click on the Ellipsis button. In the expression builder that pops up. Expand the variables and drag the User::CheckpointPath variable to the expression window. Click the “Evaluate Expression” button to ensure your expression evaluates as you expect. Click “OK” to save and exit the expression builder.
Figure 4 The expression as it should look when setting the directory to be pulled from the CheckPointPath variable we set up earlier.
· Under “Retrieve File Name,” select “Name and Extension.”
Figure 5 Configuration of the "Collections" in the Foreach loop.
· Click on “Variable Mappings” in the list box. For “Variable,” use the User::FileName variable you created within the scope of the Foreach loop.
Figure 6 The foreach loop will only retrieve one value, so index 0 must map to the variable created to hold the value. In our case, we use the FileName variable we created in the scope of the Foreach loop.
· For the ReadOnlyVariables, select User::FileName and User::CheckPointFileName. These two will be used for comparison to see if they match.
· For ReadWriteVariables, select User::FileFound. This is where we will record whether or not we find the checkpoint file.
Figure 7 Configured Script page of the script task.
· Right click the constraint you just dragged from the foreach loop to the Execute package Task and click “Edit.”
· In the “Precedence Constraint Editor” that comes up, for “Evaluation Operation” choose “Constraint and Expression” in the drop down.
· For value, choose “Success”
· For Expression, type in @FileFound. This is the variable where the script task stored “true” if we found the checkpoint file. Since this is a Boolean, there is no need to do a comparison, it will evaluate to “True” or “False” without the comparison.
Figure 8 The configured Precedence Contraint Editor. With this configuration, the "Execute Package" task it constrains will only execute if the Foreach Loop was successful, and the Checkpoint file was found.
Figure 9 My MainPackage package is stored in SQL Server. I created a new connection in the Connection drop down and pointed this connection to the server where MainPackage is stored.
Figure 10 A FOR loop configured to overcome the challenge created when the package always executes the "InitExpression" after restart.