check point

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

1. 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

4. Set the CheckpointUsage property to one of two values: ifexists; always

5. 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.

官方文件