Enterprise Workflows with the Control Flow

Both of the components of the Control Flow have been discussed in Chapter 3 as well as the different types of precedence constraints. Since the Control Flow contains standard workflow concepts that are common to most scheduling and ETL tools, including DTS, the rest of this chapter will focus on the Data Flow; however, a brief look at the Control Flow parallelization and processing is warranted.

The Control Flow, as has already been mentioned, can be designed to execute tasks in parallel or serial, or a combination of the two. Tasks also are synchronous in nature, meaning that the task requires completion before handing off operation to another process. While it is possible to design a Control Flow that contains tasks that are not connected with constraints to other tasks, the tasks are still synchronously tied to the execution of the package. Said in another way, a package cannot kick off the execution of a task and then complete execution while the task is still executing. Rather, the SSIS execution thread for the task is synchronously tied to the task's execution and will not release until the task completes successfully or fails.

Note

The synchronous nature of tasks should not be confused with the synchronous and asynchronous nature of transformations in the Data Flow. The concepts are slightly different. In the Data Flow, a transformation's synchronicity is a matter of communication (how data is passed between transformations) rather than the process orientation of the Control Flow.

Integration Services allows the maximum number of parallel tasks that execute to be set on a package-by-package basis. This setting, called the MaxConcurrentExecutables, is a property of the Control Flow. Click in a blank space on the Control Flow, and then pull up the Properties window. Figure 10-4 shows the property, which is settable to a whole number.

Figure 10-4

The default setting is -1, indicating to Integration Services to add 2 to the number of processors and use that value for the number of tasks to execute in parallel. For example, if the server has four processors and the default value is used, Integration Services will allow up to six tasks to be executed in parallel. Furthermore, if the number of possible parallel executing tasks (based on Control Flow design) is more than the number of allowable parallel tasks (allowable as specified by the MaxConcurrentExecutables setting), then some of the Control Flow tasks will have to wait to execute until parallel threads are available.

Some tasks require more server resources than others, so the package Concurrency should not be tied directly to the number of processors that your server contains. Rather, task workload should be evaluated across the tasks. For example, if your package contains a Data Flow, then most likely it will consume more server resources than the other tasks. In fact, each Data Flow can be set up to use multiple threads during execution, a property that is described in more detail in the Data Flow section that follows.