Chapter 9: Reliability and Scalability

Reliability and scalability are goals for all your systems, yet they may seem like a strange combination for a chapter. Often, though, there are direct links, as you will see. Errors and the unexpected conditions that precipitate them are the most obvious threats to a reliable process. There are several features of SQL Server 2005 Integration Services that allow you to handle these situations with grace and integrity, keeping the data moving and systems running. Error outputs and checkpoints are the two features you will focus on in this chapter, and they highlight to you how these can be used in the context of reliability. The implementation of these methods can also have a direct effect on package performance, and therefore scalability, and you will learn how to take into account these considerations for your package and process design. The ability to provide checkpoints does not natively extend inside the Data Flow, but there are methods you can apply to achieve this. The methods can then be transferred almost directly into the context of scalability, allowing you to partition packages and improve both reliability and scalability at the same time. All of these methods can be combined, and while there is no perfect answer, you will look at the options and acquire the necessary information to make informed choices for your own SSIS implementations.

Restarting Packages

Everyone has been there — one of your overnight Data Transformation Services (DTS) packages failed overnight and you now have to completely rerun the package. This is particularly painful if some of the processes inside the package are expensive in terms of resources or time. In DTS, the ability to restart a package from where it left off did not exist, and picking apart a package to run just those tasks that failed was tedious and error-prone. There have been a variety of exotic solutions demonstrated, such as a post-execution process that goes into the package and re-creates the package from the failed step onward. Although this worked, it required someone with a detailed knowledge of the DTS object model, which most production DBAs did not have. If your process takes data from a production SQL Server that has a very small window of ETL opportunity, you can almost guarantee that the DBA is not going to be pleased when you tell him you need to run the extract again and that it may impact his users.

For this reason, the introduction of "Package Restartability" or checkpoints in SQL Server 2005 is manna from heaven. In this chapter, you are going to learn everything you need to know to make this happen in your SSIS packages.

Checkpoints are the foundation for restarting packages in SSIS, and they work by writing state information to a file after each task completes. This file can then be used to determine which tasks have run and which failed. More detail about these files is provided in the "Inside the Checkpoint File" section. To ensure that the checkpoint file is created correctly, there are three package properties and one task property that you must set, and they can be found on the property pages of the package and task. The package properties are as follows:

• CheckpointFilename: This is the file name of the checkpoint file, which must be provided. There are no specific conventions or requirements for the file name.

• CheckpointUsage: There are three values, which describe how a checkpoint file is used during package execution:

  • Never: The package will not use a checkpoint file and therefore will never restart.

  • 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.

  • Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

• SaveCheckpoints: This is a simple Boolean to indicate whether checkpoints are to be written. Obviously this must be set to true for this scenario.

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. You'll see an example of this happening later.

Note

As you know, SSIS packages are broken down into Control Flow and Data Flow. 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. Implementing your own checkpoint and restart feature for data is described later in the chapter.

Remember also that if nothing fails in your package, no file will be generated. You shall have a look later at the generated file itself and try to make some sense out of it, but for now, you need to know that the file will contain all the information needed by the package when it is restarted to behave like nothing untoward had interrupted it. That's enough information to be able to make a start with using checkpoints in your packages, so now you can proceed with some examples.

Simple Control Flow

The basic idea of this first example package is that you have three ExecuteSQL tasks, as shown in Figure 9-1.

Figure 9-1

The second of those tasks, aptly named "2," is set to fail with a divide-by-zero error, as you can see in the Task Editor, shown in Figure 9-2.

Figure 9-2

The task labeled "1" is expensive, so you want to make sure that you don't need to execute it twice, if it finishes and something else in the package fails. You now need to set up the package to use checkpoints and the task itself. First, set the properties of the package that you read about earlier, as shown in Figure 9-3.

Figure 9-3

Now you need to set the properties of the task to use checkpoints, as you saw earlier (see Figure 9-4).

Figure 9-4

Now you can execute the package. The expected outcome is shown in Figure 9-5 — the first task completes successfully (green), but the second task fails (red).

Figure 9-5

If you had created this package in DTS, you would have had to write some logic to cope with the failure in order to not have to execute task 1 again. Because you are working in SSIS and have set the package up properly, you can rely on checkpoints. When the package failed, the error output window said something like this:

 SSIS package "PackageSimple.dtsx" starting. Information: 0x40016045 at PackageSimple: The package will be saving checkpoints to file "C:\chkSimple3ExecuteSQLTasks.chk" during execution. The package is configured to save checkpoints. Information: 0x40016047 at 1: Checkpoint file "C:\chkSimple3ExecuteSQLTasks.chk" was updated to record completion of this container. Error: 0xC002F210 at 2, Execute SQL Task: Executing the query "select 1/0" failed with the following error: "Divide by zero error encountered.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: 2 Warning: 0x80014058 at PackageSimple: This task or container has failed, but because FailPackageOnFailure property is FALSE, the package will continue. This warning is posted when the SaveCheckpoints property of the package is set to TRUE and the task or container fails. SSIS package "PackageSimple.dtsx" finished: Failure. 

As you can see, the output window says that a checkpoint file was written. If you look at the file system, you can see that this is true, as shown in Figure 9-6. You'll have a look inside the file later when you have a few more things of interest in there, but for the moment, just know that the package now knows what happened and where.

Figure 9-6

Now you need to fix the problem by removing the divide-by-zero issue with the second task and run the package again. Figure 9-7 shows what happens when you do that.

Figure 9-7

Task 2 was executed again and then task 3. Task 1 was oblivious to the package running again.

Earlier you saw that the task you want to be the site for a checkpoint must have the FailPackageOnFailure property set to true, otherwise no file will be written, and when the package executes again it will start from the beginning. Here is how that works. Set the task up to not use checkpoints by setting this property to false, as shown in Figure 9-8.

Figure 9-8

Execute the package once again, setting up task 2 to fail with a divide-by-zero error. No checkpoint file is written, as you expected. This means that after you've fixed the error in the task and rerun the package, the results look like Figure 9-9 (all tasks are green), which may or may not be what you want.

Figure 9-9

This example has been a very simple one and has simply involved three tasks joined by workflow. Hopefully this has given you an idea about restartability in packages; the examples that follow will be more complicated and involved than this one.

Containers within Containers and Checkpoints

Containers and transactions have an effect on checkpoints. You'll demonstrate that in this example and change some properties and settings while you're at it. First, create a package using sequence containers and checkpoints. In this package you have two sequence containers, which themselves contain ExecuteSQL tasks, as you can see in Figure 9-10.

Figure 9-10

Make sure the package has all the settings necessary to use checkpoints, as in the previous example. On the initial run-through of this package, the only container that you want to be the site for a checkpoint is task 3, so set the FailPackageOnFailure property of task 3 to true. Figure 9-11 shows what happens when you deliberately set this task to fail perhaps with a divide-by-zero error; see the earlier example to see how to do that.

Figure 9-11

As expected, task 3 has failed, and the sequence container, seq2, has also failed because of this. If you now fix the problem with task 3 and re-execute the package, you will see results similar to those shown in Figure 9-12.

Figure 9-12

So there's no real difference here from the earlier example except that the sequence container "seq 2" is also colored green. Now you'll change the setup of the package to see the behavior change dramatically. What you're going to do is make the sequence container "seq 2" transacted. That means you're going to wrap "seq 2" and its child containers in a transaction. You will now see how to do that. Change the properties of the "seq 2" container to look like Figure 9-13.

Figure 9-13

The "seq 2" container has its TransactionOption property set to Required, which means that it will start its own transaction. Now open the two child ExecuteSQL tasks and set their TransactionOption properties to Supported, as shown in Figure 9-14, so that they will join a transaction if one exists.

Figure 9-14

Now execute the package again. On the first run-through, the package fails as before at task 3. The difference comes when you fix the problem with task 3 and re-execute the package. The result looks like Figure 9-15.

Figure 9-15

As you can see, because the container was transacted, the fact that task 3 failed is not recorded in the checkpoint file. The fact that the sequence container failed is recorded instead; hence the sequence container is re-executed in its entirety when the package is rerun.

Variations on a Theme

You may have noticed another property in the task property pages next to the FailPackageOnFailure property — the FailParentOnFailure property. In the previous example, the "seq 2" container is the parent to the two ExecuteSQL tasks 2 and 3. You'll run through a few variations of the parent/child relationship here so that you can see the differences. In each example, you will force a failure on the first run-through; you will correct whatever problem there is and then run the package through a second time.

Failing the Parent, Not the Package

So what happens then if instead of setting the FailPackageOnFailure property of task 3 to true, you set the FailParentOnFailure property to true? After fixing the issue, on the re-execution of the package the whole package will be run again. Why? Because no checkpoint file has been written.

Note

Remember that if you want a checkpoint file to be written, the task that fails must have the FailPackageOnFailure property set to true; otherwise no file is written.

Failing the Parent and the Package

In this variation, you still have a transacted sequence container and you still have task 3's FailParentOnFailure property set to true. What you also have is the "seq 2" sequence container's FailPackageOnFailure property set to true. Figure 9-16 shows what happens on the rerun of the package after a failure.

Figure 9-16

As you can see, the sequence container executes in its entirety and the output window from the package confirms that you used a checkpoint file and that you started a transaction.

 SSIS package "PackageContainerFailures.dtsx" starting. Information: 0x40016046 at PackageContainerFailures: The package restarted from checkpoint file "C:\Restartability\CheckPoint Files\ContainerTest.chp ". The package was configured to restart from checkpoint. Information: 0x40016045 at PackageContainerFailures: The package will be saving checkpoints to file "C:\Restartability\CheckPoint Files\ContainerTest.chp" during execution. The package is configured to save checkpoints. Information: 0x4001100A at seq 2: Starting distributed transaction for this container. Information: 0x4001100B at seq 2: Committing distributed transaction started by this container. SSIS package "PackageContainerFailures.dtsx" finished: Success. 

Failing the Task with No Transaction

Remove the transactions from your package and simply run through this package again, getting it to fail the first time around at task 3; fix the problem and then re-execute the package. Remember that task 3 has its FailParentOnFailure property set to true, and the "seq 2" sequence container has its FailPackageOnFailure set to true. The outcome, shown in Figure 9-17, is not exactly what you expected. The sequence container has executed but nothing within has. The usage case for this scenario at the time of this writing escapes us.

Figure 9-17

Failing the Package, Not the Sequence

You may think that if the tasks 2 and 3 have the sequence container as a parent, then the package itself must be the parent of the sequence container. If this is the case, would setting FailParentOnFailure on the sequence container not be the same as setting FailPackageOnFailure on the same container? The quick answer is no. If you try this option, you will see no checkpoint file being written, and by now you know what that means. The message here is that if you want a checkpoint file to be written, then make sure that the place you want to set as a restart point has FailPackageOnFailure set to true.

The following table summarizes all the cases you've looked at so far.

Test	Summary
Simple Control Flow	Three ExecuteSQL tasks in a row, joined by workflow. The middle of the three is set to fail. Set up the package and the tasks to use restarts. Run it through once; see the task fail and the checkpoint file generated. You then fix the issue with task 2 and rerun the package, which starts from task 2 now.
Containers within Containers and Checkpoints	Placing tasks within sequence containers on the package and having task 3 fail. The main point of this example is to watch the different effect that a transaction on the sequence containers has on the point of restart after a failure.
Failing the Parent, Not the Package	Instead of having task 3's FailParentOnFailure property set to true, you have its FailParentOnFailure property set to true. The effects of this can be seen on restarts.
Failing the Parent and the Package	Again in a transacted sequence container (seq 2), you have task 3 fail. The variation here is that the task has its FailParentOnFailure set to true and the sequence container itself has the FailPackageOn- Failure property set to true.
Failing the Task with No Transaction	This is the exact same setup as the example above except the sequence container has not initiated a transaction.
Failing the Package, Not the Sequence	Show the difference between FailParentOnFailure and FailPackageOnFailure when your parent is the package itself. Isn't this logically the same thing?

Inside the Checkpoint File

Earlier it was mentioned that you would look inside the file and see what is actually inside once you had more things to put in there. In the package shown in Figure 9-18, although you have only three tasks, you also have a variable value being changed. The purpose of this package is to show you what kind of information is stored in a checkpoint file. To add a variable, simply click on the designer while in Workflow and choose Variables from the SSIS menu.

Figure 9-18

To alter the value of a variable using the Script task, you add the variable name to the ReadWriteVariables section on the Script task's editor. You then need to add some script to change the value. Below is that script.

 Public Sub Main()    '    Dts.Variables.Item("v1").Value = 2    '    Dts.TaskResult = Dts.Results.Success End Sub 

Now, cause the package to fail as shown in Figure 9-19.

Figure 9-19

Note

Instead of spending too much time figuring out an elaborate way to make your task or container fail, you can simply set the ForceExecutionResult on the task or container to Failure.

Inside the generated checkpoint file, you should find something like this:

 <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Package><DTS:Variables DTS:Cont> <DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><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="3">2</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">v1</DTS:Property><DTS:Property DTS:Name="DTSID">{A28969A0- 0633-4D43-9325-DF54B30EBF2D}</DTS:Property><DTS:Property DTS:Name="Description">This is the variable being changed</DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables><DTS:Containe r DTS:Cont DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:Contbackground-color:d9d9d9">0CB89209DD26}" DTS:Result="0" DTS:PrecedenceMap="Y"/></DTS:Checkpoint> 

The file is better broken down into the constituent parts. The first part tells you about the package to which this file applies.

 <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Package> 

The next section of the file, the longest part, details the package variable that you were manipulating:

 <DTS:Variables DTS:Cont> <DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><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="3">2</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">v1</DTS:Property><DTS:Property DTS:Name="DTSID">{A28969A0- 0633-4D43-9325-DF54B30EBF2D}</DTS:Property><DTS:Property DTS:Name="Description">This is the variable being changed</DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable></DTS:Variables> 

One of the most important things this part of the file tells you is that the last value assigned to the variable, v1, was 2. When the package re-executes, it is this value that will be used.

The final part of the file tells you about the tasks in the package and what their outcomes were. It only tells you about the two tasks that succeeded and not the one that failed.

 <DTS:Container DTS:Cont DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:Cont DTS:Result="0" DTS:PrecedenceMap="Y"/></DTS:Checkpoint > 

The first container mentioned is the "Set GV Value to 2" task.

 <DTS:Container DTS:Cont DTS:Result="0" DTS:PrecedenceMap=""/> 

The next and final task to be mentioned is the "Set GV Value to 1" task.

 DTS:Container DTS:Cont DTS:Result="0" DTS:PrecedenceMap="Y"/></DTS:Checkpoint > 

That concludes your whirlwind tour of package restartability in SSIS. Hopefully you will get something out of it, because using the features will save you hours of reloading time.