SSIS error recovery tool: CheckPoint

Everyone should be familiar with the use of SSIS data interface, students who have not heard of it have to work hard to make up. In terms of development experience and efficiency, SSIS is a very cool software, and it is still necessary to learn its basic usage. Since it is a data interface, it is inevitable to consider disaster recovery. Fortunately, SSIS provided us with the OOB Feature--CheckPoint, which can still hug its thighs at a critical time.

CheckPoint, the Failover component of SSIS. By configuring the CheckPoint of the SSIS Package, we can easily guarantee that the Control Flow will not be executed repeatedly and will only continue at the last error point. With such a disaster recovery mechanism, can we still face the complex data synchronization needs without confidence? Another valuable aspect of CheckPoint is that the CheckPoint configuration is very simple, just modify a few properties to get it, we can also experience the joy of a pickpocket programmer.

Next, I will take you to see 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, SaveCheckpoint.

CheckpointFileName is used to specify the path to save the file. If Packge is running and there is an error and interrupt, then this file will keep a running snapshot when it is interrupted. CheckpointUsage is used to execute Checkpoint execution rules, which are enabled each time, or only if the Checkpoint file exists. SaveCheckpoint is the name, and the key attributes of Checkpoint must be enabled.

2) Configure the Checkpoint properties of Control Flow, which are: FailPackageOnFail, FailParentOnFailure.

FailPackageOnFail means that if the Control Flow is abnormal, the operation will be interrupted and the control flow will continue to execute next time. FailParentOnFailure means that if the Control Flow is abnormal, the operation is interrupted, and the parent of the Control Flow is executed next time. If the parent contains multiple control flows and we want to rerun all the control flows under the container every time the exception occurs. If we are, we have to set this familiar.

Let's do two small experiments: 1) Turn on CheckPoint on the Package. 2) Repeat the execution of all Control Flows in the sequence on the Checkpoint package.

Example 1

First execution

Execute again after a disaster

Example 2

First execution

Execute again after a disaster