Home > SSIS > Rerun SSIS package with Checkpoints.

Rerun SSIS package with Checkpoints.


Checkpoint

Checkpoint in SSIS package helps to restart failed packages from the point of failure, instead of rerunning the whole package.

If the package runs successfully, the checkpoint file will be deleted, and  re-creates it when the package  start or run in the next execution.

The completion of any child containers of transacted container is not recorded in the checkpoint file. So, the child containers will be executed again while we rerun the package and that will start from the point of failure (that is transaction container).

This checkpoint is a XML file that stores about the package failure point and also stores the package variable and values except object data type. So, we should consider the security issues if the file is stored in file system. Primary option to protect this file is the operating system access control list (ACL) to secure the location or folder where you store the file.

We should use the correct checkpoint file to avoid misconfiguration of package properties at the time of failure. Because, package will refer to the checkpoint file while rerun the package.

We can only start the package from the control flow. So, we should have multiple data flow as per the business requirements to facilitate the start at the failure point feature.

Benefits

I could say that the simplest and most powerful benefits of checkpoint is to utilizing resources properly and save lot of maintenance windows time . For instance, if an ETL team upload bulk data into SQL Server everyday by downloading text files from the FTP server(s). In this case, if a particular files bothers the download and leads to package failure then we need to rerun the package from the start.

Otherwise, using checkpoint in the packages is the brilliant way to kept the execution flow tidy.

Checkpoint usage is mainly go along with the ETL framework. So, plan this feature in the ETL logical design layer.

SSIS Package properties that we use to configure the checkpoint are:

  1. CheckpointFileName (Package property) – Check point XML file name and path
  2. CheckpointUsage  (Package property- Use check point Never/Always/IfExists
  3. SaveCheckpoints (Package property) – Grant permission to have checkpoint or not. (True/False)
  4.  FailPackageOnFailure (Task property) – Indicate the package failure when any task fails. values are True/False
  5. ForceExecutionResult (Task and Package property) – Provide the Execution result forcefully and any occasion. values are None/Success/ Failure/ Completion.

Implementation

Mandatory step before we create checkpoints.

  1. FailPackageOnFailure property must be true in all containers to set the checkpoint/restart point.

In this example, I will be showing you to configure checkpoint in SSIS package and show how checkpoint will treat the package after failure .

Step 1: Create a SSIS project and add a package with 3 sequence containers and connect all. And, set FailPackageOnFailure property value to true to all containers.

Step 2: Now, Step up the checkpoint properties of package as shown below image.

Step 3: Change the property value of  sequence container 1 ForceExecutionResult to Failure.

Step 4: Run the package now.

Step 5: Check for the SSISCheckpoint file at the given location. And, change the property value of  sequence container 1 ForceExecutionResult to none then execute.

Step 6: Check for checkpoint file now. It will not be available at this time.

Note that if a package configured with checkpoint and it fails, then, the checkpoint file will be created and if the same package rerun successfully then the checkpoint file will be deleted automatically.

SSISCheckPoint

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{9EB43566-A3A6-4898-AA7A-7FFBDA43EC74}">

<DTS:Variables DTS:ContID="{9EB43566-A3A6-4898-AA7A-7FFBDA43EC74}"/>

<DTS:Container DTS:ContID="{D724C14C-1812-4293-9086-98E72468259B}" DTS:Result="0" DTS:PrecedenceMap=""/></DTS:Checkpoint>

Thanks for reading. Learn more about checkpoint.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: