Configuring Execution Properties

Execution properties modify and control how entire packages as well as individual tasks execute. In this section, you will learn to configure the number of tasks that will be executed in parallel, the execution priority of packages and tasks , and the point at which a task failure constitutes a package failure. You will also learn how to work with tasks that are not free-threaded.

Configuring Parallel Execution of Tasks

In Chapter 2, you learned about the use of precedence constraints to control the order in which tasks execute within a package. You also learned that tasks that are not limited by precedence constraints execute in parallel unless the tasks share the same connection object. By definition, tasks that share a connection object execute serially because only one task can use a connection object at any point in time.

After you determine which tasks you want to execute in parallel and you ensure that each task needing to execute simultaneously has a separate connection object, you must decide how many tasks you actually want DTS to execute in parallel. By default, the maximum number of tasks that DTS can execute in parallel is four. You can increase the performance of a well-designed data movement application on a multiprocessor computer by increasing the number of steps that execute concurrently in each package. If, however, you set the number of concurrently executing steps too high, the overall performance of the data movement application can suffer because the processors will be switching between tasks rather than finishing each task sequentially.

You can use DTS Designer to configure the maximum number of tasks that execute concurrently in a package in the Properties dialog box for the package. Figure 5-1 displays the properties of the MasterUpdate package that you created in Chapter 4.

Figure 5-1: Properties of the MasterUpdate package

Notice that the MasterUpdate package is configured to execute up to four tasks in parallel. You can increase or decrease the number of tasks that will execute in parallel at run time by dynamically changing the MaxConcurrentSteps property of the package. (For more information about changing package and task properties at run time, see Chapter 6.)

In a modularized data movement application, you can control the number of subpackages that are executed simultaneously from a master package by configuring the number of tasks that execute concurrently. Figure 5-2 displays the MasterUpdate package that you created in Chapter 4. This package contains two Execute Package tasks that update dimensions in the Sales cube. In a complex Analysis Services cube, you might have 15 to 20 dimensions, each of which is updated using a separate subpackage.

Figure 5-2: MasterUpdate package

The two subpackages called by this master package will execute concurrently because the package does not contain precedence constraints, the number of packages in the package does not exceed the number of tasks that can execute in parallel, and the tasks in the subpackages have their own connection objects. As you add Execute Package tasks to a master package, you will quickly exceed the number of packages that executes concurrently unless you increase the setting for parallelism. However, the number of tasks that you actually want to execute in parallel on any particular computer depends upon the number of processors on that computer and the other tasks on that computer that are competing for processor resources. You can increase or decrease the number of subpackages that executes concurrently in your modular data movement application by using only the parallelism setting. This design enables you to develop and test the data movement application on one computer and then tune its performance in the production environment without having to edit each individual subpackage.

Configuring Execution Priority

Another execution property that you can configure is the execution priority of the operating system thread on which a package and its subpackages run. By default, packages execute at normal priority relative to other applications running on the same server. Increasing the execution priority for the package might improve its performance, but potentially at the expense of the responsiveness of other applications. However, if the data movement application executes a multi- processor computer, you can configure the computer to execute SQL Server tasks only on particular processors. In this environment, you can increase the execution priority of the packages in the data movement application relative to other SQL Server tasks. On the other hand, if the data movement application is using too many resources relative to other tasks on either a single or a multiple processor computer, you can decrease the execution priority of the data movement application, albeit at the price of poorer performance.

You can use DTS Designer to increase or decrease the priority of a package relative to other applications in the package s Properties dialog box, which was shown in Figure 5-1. You can also set this value at run time by changing the PackagePriorityClass property of the package.

By default, all package tasks inherit the thread priority of the package, and tasks that execute in parallel execute with equal priority. You can use DTS Designer to increase or decrease the priority of a task within a package relative to other tasks on the Options tab of the task s Workflow Properties dialog box. Figure 5-3 displays this dialog box for the DTSStep_DTSExecutePackageTask_1 step in the MasterUpdate package. This is the step that executes the Execute Package task and calls the UpdateProductDim subpackage. Execution priority is determined by a property of the step; it is not a function of the task itself.

Figure 5-3: Options tab of the Workflow Properties dialog box for the DTSStep_DTSExecutePackageTask_1 step

Important  

Tasks and steps are related but different. Whereas a task defines a unit of work within a package, a step actually executes the task. In the first half of this book, you are learning about tasks. In the second half of this book, you will learn about manipulating the execution of these tasks by using the step object associated with the task.

You can also set this task priority value at run time by changing the RelativePriority property of the step. In the data movement application you are building, all packages and tasks will execute at normal priority.

Configuring the Definition of Package Failure

Another execution property that you can set is the definition of package failure. If a package completes all the tasks within it (regardless of their success or failure), the package completes successfully unless you define the failure of a particular task as a package failure. You can use DTS Designer to configure a task so that its failure will cause the entire package to fail. You set this definition on the Options tab of the task s Workflow Properties dialog box. (See Figure 5-3.) If you call a subpackage from a master package by using the Execute Package task and configure the failure of one of its steps to cause it to fail, the failure of the step in the subpackage will cause the failure of the master package and all its subpackages. In the data movement application you are building, you will not configure the failure of any particular task to cause the failure of the entire data movement application. Rather, you will use error handling routines to capture error information to enable you to recover from errors. For more information, see Chapter 8.

Tip  

Configuring a package to fail when one of its steps fails does not undo any steps performed by the package and its subpackages before the step failure occurs. To automatically roll back package changes when a package failure occurs, you must create one or more transactions in the data movement application and have tasks enlist in an ongoing transaction. (For more information, see the section Working with Transactions later in this chapter.)

Working with Tasks That Are Not Free-Threaded

The other execution property of a step that you might need to configure is the execution thread on which the task will execute. DTS executes each task on a separate thread unless you configure a step to execute a task on the main package thread. Spawning new threads for each task enables DTS to take advantage of the operating system s ability to distribute execution threads among available processors. Executing tasks on separate threads increases the performance of DTS packages, particularly when the tasks execute on computers with multiple processors. However, tasks that use an OLE DB provider that is not free- threaded, or that were developed using a language not supporting the Free- Threaded model (or the Apartment Threading model), must execute on the main thread. (You can use DTS Designer to specify whether a task executes on the main thread or on its own thread by modifying the properties of the step that executes the task in the task s Workflow Properties dialog box.) Microsoft Visual Basic tasks, such as the Analysis Services Processing task or custom Visual Basic tasks, are not free-threaded. The following providers are not free- threaded:

• Microsoft OLE DB Provider for Jet

• Microsoft Excel provider

• dBase provider

• Paradox provider

• HTML source files provider

  Important  

  Attempting parallel execution on a provider that does not support it can result in serious errors.

Rather than run tasks that are not free-threaded on the main thread, you can choose to run these tasks in a separate process by using the Execute Process task to execute them. For example, to process multiple Analysis Services cubes in parallel, you must run each execution of the Analysis Services Processing task in a separate process.