SSIS error recovery tool: CheckPoint, ssischeckpoint

SSIS error recovery tool: CheckPoint, ssischeckpoint

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

In SQL, The ssis Import and Export wizard is different from the ssis package

In sqlserver2005, data is imported and exported through SQL Server 2005 Integration Services (SSIS:
In Business Intelligence Development Studio, right-click the "SSIS package" folder and click "SSIS Import and Export wizard ".
Or
On the "project" menu in Business Intelligence Development Studio, click "SSIS Import and Export wizard ".
Or
In SQL Server Management Studio, connect to the database engine Server type, expand the database, right-click a database, point to "task", and then click "import data" or "export data ".
Or
Run DTSWizard.exe (in C: \ Program Files \ Microsoft SQL Server \ 90 \ DTS \ Binn) in the Command Prompt window ).
==========
You may need to install SQL Server 2005 Integration Services (SSIS)

About ssis package:
An amazing feature of SQL Server Integration Services (SSIS) is the Checkpoint, which enables the re-executed package to be executed from the original failure point. When the execution package fails, the Checkpoint can save this information in the checkpoint document. When it is executed next time, you can decide where to continue the execution.

Of course, the benefits are obvious. First, some tasks will be executed for a long time, and re-execution will be inefficient if an error occurs later. In addition, it is a good application of this function. We can combine some atomic and inseparable tasks so that we can either fail or succeed together, which is easy to control.

Checkpoints are generally defined in the ETL process, but they can be effectively used without being defined at will. They must be used in a reasonable control flow in a planned manner.

Checkpoint company profile?

CHECKPOINT Company Profile

Date of establishment: 1993
US headquarters: Redwood City, California
International Headquarters: lamogan city, Israel
Number of employees: More than 1180
Date of release: January 1, June 28, 1996

NASDAQ transaction code: CHKP

Check Point Software Technology Co., Ltd. is a world-leading company in the internet security field. The security Virtual Network (SVN) architecture of Check Point Software Technology Limited provides infrastructure that supports secure and reliable Internet communication. Through the Internet, Intranet, and exists, SVN ensures secure communication between networks, systems, applications, and users. The SVN solution released in the company's "Next Generation" product series further enhances the business communication and resource security of the company's network, remote employees, offices, and the partner's external network. The open security platform (OPSEC) of Check Point provides an advanced framework to integrate with the excellent solutions of more than 300 industry-leading partners and achieve high interoperability.