SSIS: Using Checkpoints

When working in SSIS, one of the most frustrating things is when your package has several steps in the control flow, and fails on the last one. Especially if the first few steps take a longer amount of time, and the last step is an archive or something that takes 5 seconds, and someone deleted the folder or you ran out of space, etc. Fortunately, in SSIS there is a way around this issue, but unfortunately it’s not used nearly as often as it should be. In my opinion, this should be a requirement of every package in production that has more than 1 step in the control flow. They’re called Checkpoints, and they’re very simple to set up.

 

To start, let’s create a simple SSIS package and add on 3 script tasks. You can use any kind of component, I’m just adding script tasks as they’re the only component in the control flow that requires no configuration. Your package should look something like:

 

image

 

Right click in the gray area on the control flow, and go to Properties. At the very top are 3 properties pertaining to checkpoints:

 

image

 

The third property, Save Checkpoints, controls the other 2. While this is set to False, the other 2 properties are turned won’t work. Change the Save Checkpoints property to True, the Checkpoint Usage property from Never to IfExists, and give your checkpoint file a name. The completed property window should look like:

 

image

 

Additionally, you also need to set what components in the control flow will actually fail the entire package and generate the checkpoint file. Click on the “Script 3” component, and change the “FailPackageOnFailure” property to True:

 

image

 

Do this for each component you would want to trigger a checkpoint file. That’s it! Now let’s test our checkpoint and make sure it works. Under the same Script 3 component, change the property “ForceExecutionResult” to Failure. This will make the task always fail, no matter what. Then run the package, and the third component should fail:

 

image

 

Navigating to the checkpoint path you specified, you should now see a checkpoint file with the name you specified earlier:

 

image

 

This is what SSIS will use when it restarts to determine where it left off at. Change the ForceExecutionResult property back to none, and rerun the package. The next run will only cause the third component to execute, and leave the first two components alone:

 

image

 

Navigate back to the checkpoints path, and you’re checkpoint file is gone.

 

image

 

Since the execution finished successfully, the checkpoint file was deleted so that the next time the package runs, it will start from the beginning. To demonstrate, run the package again and all 3 components will run:

 

image

 

In conclusion, by using checkpoints in our SSIS packages we can add some restart-ability to our packages so that in the event of failure the package can just pick up where it left off, instead of having to run the entire thing over again. Very useful in production scenarios where packages are doing multiple things.

Advertisements

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