Check Points on SSIS package

Suppose we are running the package and it got failed after completing more than 95%. Again we need to re-run this package. We need to wait to complete it. Suppose once again it got failed. It is time consuming. Let assume is any mechanism that save my executing result and when we running remaining failed package (5%), it will save the time as well as efforts. In SSIS we have a package properties know as Check points.

Checkpoints force SSIS to maintain a record of the control flow executable that have successfully run. In addition, SSIS records the current values of user-defined variables. This current context will be stored in an XML file which is defined in the package property window. After that, if a package fails during execution, SSIS will reference the checkpoint file when we are trying to rerun the package. It will first do so by retrieving the current variable values as they existed prior to package failure and, based on the last successful executable that ran, start running the package where it left off. That is, it will continue executing from the point of failure, assuming.

Integration Services can restart failed packages from the point of failure, instead of rerunning the whole package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package is run.

Checkpoints only happen at the Control Flow; it is not possible to checkpoint transformations or restart inside a Data Flow. The Data Flow Task can be a checkpoint, but it is treated as any other task.

Let’s learn how we use Checkpoints in Our package

Suppose I am having three tasks out of them two has been completed successfully and third one gets failed. If task has failed means package will failed. With the help of the Checkpoints we can run the failed task.

Here I am taking 3 tasks

  

And I am trying to insert a record on the table. No I am Creating connection Manage for all.

  

I have created correct connection.

Now I am executing this package.

  

Package executed successfully.

Let’s see the result.

  

Now I make some changes on the task 3. It is the cause of error.

  

I am not having the runner5 table in my database. It will defiantly get failed. Let’s run the package.

  

Let’s see the error

  

Errors seem that invalid object runner5. I mean this table does not exist on the database.

Suppose if you want to run the failed task only in such scenario we need to implement the Checkpoints.

Implements Check Points on the Package

Click on the package properties.

  

The following items need to be configured on the properties tab for SSIS package:

CheckpointFileName

Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.

  

Create the Checkpoints file.

CheckpointUsage 

 Determines if/how checkpoints are used. Choose from these options:

Ø  Never(default),

Ø   IfExists

Ø  Always.

1) Never: The package will not use a checkpoint file and therefore will never restart.
2) If Exists: If a checkpoint file exists in the place you specified for the CheckpointFileName property, then it will be used, and the package will restart according to the checkpoints written.
3) Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

Select the If Exists option.

  

Save Checkpoints

Select true.

  

Now we need to set the FailPackageOnFailure properties on each task.

The one property you have to set on the task is FailPackageOnFailure. This must be set for each task or container that you want to be the point for a checkpoint and restart. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.

  

Now I am executing the package.

  

Now see

  

One file is created.

This is a checkpoints file. If the package is fail then the file is created. If the package is completed successfully executed then the file is not created. This is an XML file.

  

Now I am correcting the error.

  

Now I am executing this package.

See only 3rd task executed.

  

Now see the checkpoints file gone.

Some Example where we need to use Checkpoints

1. A package that downloads multiple large files by using an FTP task for each download can be restarted after the downloading of a single file fails and then download only that file.

2. A package that performs bulk inserts into dimension tables in a data warehouse using a separate Bulk Insert task for each dimension can be restarted if the insertion fails for one dimension table, and only that dimension will be reloaded.

       Checkpoints doesn’t work properly when a SSIS package contains

Ø  Complex logic

Ø  Iterations/Loops

Ø  Transactions Enabled

Ø  “Object” type variables

Ø  Parallel execution