Before starting it, Let’s make sure that package runs. Place both text files in the E:ClientsFiles folder. Run the package and see whether the files are copied to the destination and messages from both Script Tasks are displayed. It should work. Okay, let’s start testing the checkpoint. Remove the Transaction2.txt file from the source folder and run it. The output will be something like this:
The task Load Transaction2 File failed because of the unavailability of the Transaction2.txt file. We can run the package again once the file is available but the biggest problem is, it runs the first task again that loads Transaction1.txt too. Because of that, it may introduce data (or business) errors or redundancy (if the transformation/transferring is implemented). This is what we want to avoid and this is where we have use checkpoint files. If the package fails, we need to restart the package from the point of failure, not from scratch.
Let’s configure it. Change the properties as illustrated below:
| Property | Value | |
| Package | ||
| CheckpointFileName | D:CheckpointFilesSamplePackageCheckpoint.xml | |
| CheckpointUsage | IfExists | |
| SaveCheckpoints | True | |
| All Other Tasks | ||
| FailPackageOnFailure | True |
Let’s run the package again. Make sure the source folder contains only the Transaction1.txt file. Just like the first time, the package fails after the second task. Now open the folder which is supposed to hold the checkpoint file. You will notice that SamplePackageCheckpoint.xml has been created. This file has been created because we have set the value as True for SaveCheckpoints in the package. It has not been erased due to the failure in the package. The failure resulted because of the FailPackageOnFailure property of the Load Transaction2 File is set to True.
Place the Tranaction2.txt file in the source folder and run the package. You will notice that the package starts from the Load Tranaction2 File task and continues:
It started from the third task because the checkpoint file instructs it to, and the package has used this checkpoint because the CheckpointUsage property was set to IfExists. If you check the checkpoint file’s folder, you will not see the file now. It has been deleted since the package was successful.
Checkpoint usage with for loop
Checkpoint settinga are different with for loops. I came across this while trying to implement it in one of my ETL test applications. To understand it, let’s try out some simple code.
Open a new SSIS project and add a For Loop Container. Create a variable called Year and set the type as Int32. Set the default value of the Year as 2004. Think about a scenario where you need to do some processing from a given year to the current year. So the given year would be the value in the Year variable. Add a script task onto the For Loop Container that represents this process. Add the Year variable in the ReadOnlyVaribles of the ScriptTask and add a message box that shows a message like below:
‘ code 1
MsgBox(“Calculation done for year ” & Dts.Variables(“User::Year”).Value.ToString() & “.”)
Dts.TaskResult = Dts.Results.Success
Now configure the For Loop Container like below. Note that we do not set the value for @Year at the InitExpression. The initial value should be taken from the variable.
Done! Run the package. You will see messages for year 2004, 2005, 2006, and 2007. Let’s configure the checkpoint file now. Set properties like below:
| Property | Value | |
| Package | ||
| FailPackageOnFailure | True | |
| CheckpointUsage | IfExists | |
| SaveCheckpoints | True | |
| For Loop Container | ||
| FailPackageOnFailure | True | |
| Script Task | ||
| FailPackageOnFailure | True |
Since we need to fail the package at a certain point, change the code of the Script Task like below.
‘ code 2
If (CInt(Dts.Variables(“User::Year”).Value) = 2005) Then
MsgBox(“Error occurred while processing for ” & Dts.Variables(“User::Year”).Value.ToString() & “.”)
Dts.TaskResult = Dts.Results.Failure
Else
MsgBox(“Calculation done for year ” & Dts.Variables(“User::Year”).Value.ToString() & “.”)
Dts.TaskResult = Dts.Results.Success
End If
Run the package again. The Package fails after processing year 2004. You will see that checkpoint file has been created and has not been removed. Change the code of the Script Task as it was (code 1). Since the package ended up in failure and a checkpoint file is available, if we restart the package, it should start from the point of failure, it should start processing 2005. Restart the package. It starts from point of failure but it starts the processing from year 2004. This is because the checkpoint file says that the value of Year variable was 2004 at the failure, which is wrong. The value of variable Year has not been saved correctly. This happens when the values of FailPackageOnFailure property is set to true for tasks inside the For Loop Container. Get the properties of ScriptTask inside the For Loop Container and set the FilaPackageOnFailure to false. Delete the checkpoint file. Change the code of the ScriptTask back to code 2. Run the package again.
Once the package fails, change the code of ScriptTask back to code 1. The package should start from the point of failure. Check the checkpoint file created by the previous execution. You will see that the value of the variable Year has been saved as 2005. Run the package. Notice that For Loop Container starts counting from 2005, as we expected. Important point here is, we should not set the property FailPackageOnFailure to True for tasks inside the For Loop Container even if you have another For Loop Container (nested) inside.
Further, I noticed that when the For Loop Container contains more than one task that are connected via precedence constraints, when a restart after failure occurs, it starts the For Loop Container from the point of failure but starts with the first task within the loop without starting from the failed task. This happens, no matter what setting is configured; which proves that we have no way of controlling the tasks inside the for loop for checkpoints.