Enterprise integration scenarios - SQL Server 2008

For the majority of enterprise integration scenarios, it is very desirable to have a package restart where it left off in the case of a failure. Being able to start where a particular failure happened, as opposed to from the beginning of package, could save hours of unnecessary rework.

SSIS provides this support through the implementation of checkpoints. When an SSIS task fails, the new checkpoint functionality allows you to restart the package, beginning from the point of failure.

To enable checkpoints for an SSIS package, follow these steps:

  1. In the SSIS Designer, click an empty area in the Control Flow design surface to focus the Properties window on the SSIS package.
  2. In the Properties window, type in a filename for the CheckPointFileName property. This file does not need to exist already.
  3. Enable checkpoint usage for the package by selecting Always for the CheckpointUsage property.

The checkpoint file will be used to save all completed tasks, package configurations, containers, system variables, and user-defined variable information. When the SSIS package runs successfully, it deletes the checkpoint file it originally created. If you select the Always option, the SSIS package will not execute unless the checkpoint file exists. If you select the IfExists option, the package will use the checkpoint file if a previous execution failed, but if the previous execution succeeded, it will execute as normal.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics