Checkpoint Files

If you’re a production DBA or have been a support person at one point, it’s certain that you’ve had a middle-of-the-night call because of an ETL failure of some sort. You probably had to go into the server while you were groggy from lack of sleep, and try to make the necessary changes to get the package working again. For example, you may have had to clean up space on the server in order for the package to complete its load. Now comes the ultimate problem: Do you re-run the entire package or just a piece of the package, and if you only ran a piece, what piece?

If you choose the wrong answer to that question, you can accidentally reload data, causing duplication or not load data at all. It’s a bad spot for a DBA to be in, and is highly contingent on experience with the package that failed. This experience is great until the DBA who has the experience goes on vacation or quits, and someone else has to come up to speed quickly.

Checkpoint files are a tool in your SSIS arsenal to mitigate this problem. Essentially, these files allow you to rerun a package from the point of failure, and they save the state of the package at failure. That state file, which is XML, will contain which container or task last successfully executed, as well as other state information (such as the variable values). When you fix the problem that caused the package to fail and re-run the package, it will start at the step that failed, and skip over the successful tasks.

There are a few points to bring out before trying an example. Checkpoint files can only be generated in the control flow. For example, checkpoint files cannot be used to capture the state of the data flow to restart on a given transformation. To do this, you’ll need to use raw files. They can, however, capture the fact that they finished a data flow task and start the package after that step.

Let’s now illustrate how to use checkpoints through an example. In this example, you will load three records into a table. If one statement fails, you want to be able to correct the issue and then start at the failed task. First, create a simple table to load with a single column by using the following syntax in SQL Server Management Studio while connected to the AdventureWorks database:

  CREATE Table RestartabilityExample (Column1 int)

Next, create a new package called Restartability.dtsx and drag three Execute SQL Tasks onto the control flow. Connect each of the three tasks sequentially using Success precedence constraints. Also, create a connection manager that points to the same AdventureWorks database in which you created the RestartabilityExample table. As a last preparatory step, create a string variable called strTestVariable that is scoped to the entire package and has a value of Test Value. This variable will only be used to demonstrate some data in your checkpoint XML file later.

You must now configure each of the Execute SQL Tasks. Name the first Execute SQL Task that executes in the package Purge Table and double-click it to configure it. Point the task to the AdventureWorks connection manager and type the following query into the SQLStatement property:

 DELETE FROM RestartabilityExample

This will purge the table prior to inserting into it, because it will be used in further examples in this chapter. Click OK to exit the task editor. The final configuration should look like Figure 7-7.

Figure 7-7: Final configuration

Rename the task that connects to the Purge Table Task, Insert Record 1, and open the task to configure it. This time, you want to insert a single row into the example table. Point the task to the AdventureWorks connection manager and then use the following query in the SQLStatement property:

 INSERT INTO RestartabilityExample      Values(1)

Lastly, rename the final task to Insert Record 2 and again point it to the AdventureWorks connection manager. This time, set the SQLStatement property to the following query:

 INSERT INTO RestartabilityExample      Values(2)

With the package now configured to run without checkpoints, run the package to confirm that all the components work properly. The successful creation and execution of the package should look like Figure 7-8. After executing the package, go to Management Studio and confirm that you have two records in the RestartabilityExample table.

Figure 7-8: Successful creation and execution of the package

Let’s now add the checkpoint logic into the package to detect a failure. This is a two-step process: Configure the package for checkpoints and then configure the tasks to trigger the checkpoint.

First, go to the Properties window for the package (make sure nothing is selected before going to the Properties window). In the top of the Properties window, change the CheckpointFileName to C:\ExpertSSIS\Checkpoint.xml. This property tells SSIS where to output the checkpoint file. The XML extension is optional, but most people decide to go with that format since it is an XML file. Next, change the CheckpointUsage property to IfExists and the SaveCheckpoints property to True. The IfExists property tells SSIS to use the checkpoint file if it exists. The checkpoint file will not be created until a failure occurs, and that’s why you want to use the IfExists versus Always. The SaveCheckpoints property tells SSIS that if the package were to fail, then create the checkpoint file.

The next step is to configure each task that you want to participate in the checkpoint. Click each of the three tasks and go to the Properties window for each task. Then set the FailPackageOnFailure property to True for each task in the package (this is not to be confused with the FailParentOnFailure property). With this property set, the checkpoint file will be created if a failure occurs in any of the three tasks. Without this set, the checkpoint file will never be created, since a package failure event was never escalated. Execute the package again and note that the C:\ExpertSSIS\Checkpoint.xml file was never created. This is because the package successfully executed. In a moment, you’ll be shown a way to trigger errors without having to alter the TSQL code, and we’ll conclude this example by showing you the checkpoint file.

Dynamic Checkpoint File Name

In the example so far, you have hard-coded a checkpoint file name, which may be the same file being used by another package. C:\ExpertSSIS\Checkpoint.xml is a very generic name, and there’s a very good chance of a collision. Instead of doing this, it is a best practice to name your checkpoint files based on the package name like RestartabilityCheckpoint.xml.

You can do this easily dynamically by going back to the Properties window for the package and then using the Expressions dialog box to create an expression for the file name that will automatically override the static file name you set earlier. The expression will need to set the CheckpointFileName property to the following expression code, and can be seen in Figure 7-9:

 "C:\\ExpertSSIS\\Checkpoint" + @[System::PackageName] + ".xml"

Figure 7-9: Creating an expression

Testing Your Error Logic

With the package now complete and the checkpoint file name now dynamic, let’s intentionally cause an error. You can do this by using two mechanisms. One way is to change the TSQL statement in the second task to something like the following syntax. Since an A is an invalid character to pass to an integer field, the syntax will fail, causing a package failure.

 INSERT INTO RestartabilityExample      Values(A)

By taking that tactic, though, you may forget to set the syntax back to the way it was previously. A more elegant solution is to change a single property that simulates a failure. To do this, select the Insert Record 1 Task and go to the Properties window. Then, change the ForceExecutionResult property to Failure. This will cause the task to report a failure no matter if the task succeeds or fails. It’s important to note that this mechanism to simulate errors does not trigger any event handlers. It is great for testing failure precedence constraints and to test your checkpoints.

Execute the package again and notice that the package now fails on the second task. This time, you’ll also see that C:\ExpertSSIS\CheckpointRestartability.xml was created. When opening the file, you’ll see it contains the information about what task last executed successfully and information about the variable you created earlier, too. An example of the checkpoint file can be seen here, but your data may vary:

 <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID= "{}"> <DTS:Variables DTS:Cont>  <DTS:Variable>   <DTS:Property DTS:Name="Expression" />   <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>   <DTS:Property DTS:Name="Namespace">User</DTS:Property>   <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>   <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>   <DTS:VariableValue DTS:DataType="8">TestValue</DTS:VariableValue>   <DTS:Property DTS:Name="ObjectName">strTestVariable</DTS:Property> <DTS:Property DTS:Name="DTSID">{} </DTS:Property>   <DTS:Property DTS:Name="Description" />   <DTS:Property DTS:Name="CreationName" />   </DTS:Variable>   </DTS:Variables>   <DTS:Container DTS:Cont DTS:Result="0" DTS:PrecedenceMap="" />   </DTS:Checkpoint>

With the file now created, fix the simulated problem by changing the ForceExecutionResult property on the second task to None. This will set the task to its natural state. Execute the package again and you’ll see that the first task that successfully executed the first time is skipped over, as shown in Figure 7-10. When you go back to the C:\ExpertSSIS directory, you’ll see that your checkpoint file is now gone, as your package successfully executed and the checkpoint file is no longer needed.

Figure 7-10: First task that successfully executed the first time being skipped over

Keep in mind that in checkpoint files, you must plan your packages very carefully. You must ensure that the package’s purpose can still be served if you skip over the first few tasks. For example, if you have an on completion event that cleans up work from a failed previous task, but then jumps over that previous task on the next run, you may leave your data in an unknown state.