definition
At control flow level, we can configure a CHECKPOINT file in the SSIS package to log package execution information in it.
advantage
- Avoid repeating the downloading and uploading of large files/data
- Avoid repeating the loading of large amounts of data.
- Avoid repeating the aggregation of values
example
For example, a package that performs bulk inserts into dimension tables in a data warehouse using a different Bulk Insert task. If we apply check point, it can be restarted if the insertion fails for one dimension table, and only that dimension will be reloaded.
notice point
If a package is configured to use checkpoints, Integration Services captures the restart point in the checkpoint file. The type of container that fails and the implementation of features such as transactions affect the restart point that is recorded in the checkpoint file. The current values of variables are also captured in the checkpoint file. However, the values of variables that have the Object data type are not saved in checkpoint files.
Checkpoint data is not saved for For Loop and Foreach Loop containers. When a package is restarted, the For Loop and Foreach Loop containers and the child containers are run again. If a child container in the loop runs successfully, it is not recorded in the checkpoint file, instead it is rerun.
property
CheckpointFileName
Specifies the name of the checkpoint file.CheckpointUsage
Specifies whether checkpoints are used.SaveCheckpoints
Indicates whether the package saves checkpoints.
This property must be set to True to restart a package from a point of failure.
checkpoint usage
Never
Specifies that the checkpoint file is not used and that the package runs from the start of the package workflow.Always
Specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.IfExists
Specifies that the checkpoint file is used if it exists. If the checkpoint file exists, the package restarts from the point of the previous execution failure; otherwise, it runs from the start of the package workflow.
whole process
- open the package you want to save checkpoint
2.go to property, Set the SaveCheckpoints property to True
3.Type the name of the checkpoint file in the CheckpointFileName property
Set the CheckpointUsage property to one of two values: ifexists; always
Configure the tasks and containers from which the package can restart.
Right-click a task or container and click Properties.
Set the FailPackageOnFailure property to True for each selected task and container.
