SSIS error recovery tool: Checkpoint

??

You should be familiar with using SSIS for data interfaces. You have never heard of it before. In terms of development experience and efficiency, SSIS is a very cool software and it is necessary to learn its basic usage methods. Since it is a data interface, it is inevitable to consider disaster recovery. Fortunately, SSIS provides OOB's feature-checkpoint. You can hold it in the thigh when it is critical.

Checkpoint, SSIS failover component. By configuring the checkpoint of the SSIS package, we can easily ensure that the control flow is not executed repeatedly, and the execution only continues at the last error point. With such a disaster recovery mechanism, can we face complicated data synchronization demands without confidence? Another valuable aspect of checkpoint is that the checkpoint configuration is very simple. You only need to modify a few attributes to get it done. We can also experience the joy of a programmer.

Next, I will show you how to configure this checkpoint and use checkpoint for disaster recovery in a simulated data synchronization scenario.

1) to enable the checkpoint mechanism of packge, We need to configure the following attributes of the package: checkpointfilename, checkpointusage, and savecheckpoint.

Checkpointfilename is used to specify the path for saving the file. If a packge error occurs during running and the file is interrupted, the file will maintain the running snapshot during interruption. Checkpointusage is used to execute the checkpoint execution rule. It is enabled every time or only when the checkpoint file exists. The savecheckpoint is an alias. The key attribute of the checkpoint must be enabled.

2) configure the checkpoint attribute of control flow, which are failpackageonfail and failparentonfailure.

Failpackageonfail indicates that if the control flow is abnormal and the operation is interrupted, the control flow will continue to run next time. Failparentonfailure refers to the next execution of the control flow's parent if the control flow is interrupted due to an exception, if this parent contains multiple control flows and we want to re-run all control flows under the container every time an exception occurs, we need to set this familiarity.

Let's do two small experiments: 1) enable checkpoint on the package. 2) Repeat all control flows in the sequence on the checkpoint package.

Example 1

First execution

Execute again in the event of a disaster

Example 2

First execution

Execute again after the disaster

??