BIML V – Precedence Constraints


In prior posts, precedence constraints have been managed through the package setting for ConstraintMode. This assumes either a parallel execution (ie ConstraintMode=Parallel) or sequential execution (ie ConstraintMode=Linear). The ConstraintMode is a mandatory property for the package and considers the order by which tasks are defined in code.

To illustrate this we’ll add three SQL tasks and observe the generated package when the constraint mode is set.

Linear Constraint

A Linear constraint creates a precedence constraint between tasks in the order in which those tasks are added in script (as below)

<Package Name=”Precidence_Constraints” ConstraintMode=”Linear”>
  <Tasks>
	<ExecuteSQL Name=”Select 1” ConnectionName=”DB_Src”>
	  <DirectInput>Select 1</DirectInput>
	</ExecuteSQL>
	<ExecuteSQL Name=”Select 2” ConnectionName=”DB_Src”>
	  <DirectInput>Select 2</DirectInput>
	</ExecuteSQL>
	<ExecuteSQL Name=”Select 3” ConnectionName=”DB_Src”>
	  <DirectInput>Select 3</DirectInput>
	</ExecuteSQL>
  </Tasks>
</Package>

 

Parallel Constraint

 

A parallel constraint simply adds the tasks to the control flow (as below). Note that since all tasks are defined in parallel, there is no execution order (as defined by a constraint).

 

<Package Name="Precidence_Constraints" ConstraintMode="Parallel">
  <Tasks>
	<ExecuteSQL Name="Select 1" ConnectionName="DB_Src">
	  <DirectInput>Select 1</DirectInput>
	</ExecuteSQL>
	<ExecuteSQL Name="Select 2" ConnectionName="DB_Src">
	  <DirectInput>Select 2</DirectInput>
	</ExecuteSQL>
	<ExecuteSQL Name="Select 3" ConnectionName="DB_Src">
	  <DirectInput>Select 3</DirectInput>
	</ExecuteSQL>
  </Tasks>
</Package>

 

Naturally, we may want more control over the order in which tasks are executed and we can do this by overwriting a tasks default constraints (which is set in the package). That is, overwriting the ConstraintMode that we set. For example assume that we want to fire [Select 1] and then [Select 2] in parallel with [Select 3] as in the following image.

 

 

In order to do this, we need to set the precedence constraint for [Select 3] (of course I’m assuming that we have a linear constraint mode to create the constraint between [Select 1] and [Select 2]). This is a property of the task and set on the incoming task. For example, [Select 3] is defined by the following snippet.

<ExecuteSQL Name="Select 3" ConnectionName="DB_Src">
  <DirectInput>Select 3</DirectInput>
  <PrecedenceConstraints>
	<Inputs>
	  <Input OutputPathName="Select 1.Output" />
	</Inputs>
  </PrecedenceConstraints>
</ExecuteSQL>

 

Note that the precedence constraint is a node of the task and the input of the constraint (the task to be executed prior to this one) is defined by name and suffixed with “.Output” (ie OutputPathName=”Select 1.Output”).

 

Yes We Overwrite the Constraint

 

It is important to remember that we are overwriting the default constraint for the package. While this may not be an issue with parallel execution (since we need to define each constraint manually), it may be an issue for linear constraints.

 

For example, suppose that we add [Select 4] to the package and we want to have this fire in parallel with [Select 1] and beta constraint for [Select 3] (as below).

 

 

We may assume that we can just append [Select 4] as the final task in the package and specify the precedence constraints for [Select 3] as [Select 1] and [Select 2]. The pseudo xml (yes I made that up), is

 

<ExecuteSQL Name="Select 1" />
<ExecuteSQL Name="Select 2" />
<ExecuteSQL Name="Select 3" >
  <PrecedenceConstraints Inputs>
	  <Input OutputPathName="Select 1.Output" />
	  <Input OutputPathName="Select 4.Output" />
  </PrecedenceConstraints>
</ExecuteSQL>
<ExecuteSQL Name="Select 4" />

 

Unfortunately, the linear mode for the package is enforced whenever the precedence constraint is not set for a task. The above snippet would produce the following package.

 

Notice that the precedence constraint between [Select 3] and [Select 4] is dependent on its own execution? That is [Select 3] can only executed after [Select 4] and [Select 4] is dependent on [Select 3].

 

Clearly this is not the outcome that we anticipated (and in my testing it crashes visual studio).

 

If we wish to overwrite the packages default constraint (it was linear), we must explicitly set the input constraint to be nothing. This can be done with the following snippet;

 

<ExecuteSQL Name="Select 4" ConnectionName="DB_Src">
	<DirectInput>Select 4</DirectInput>
	<PrecedenceConstraints>
	  <Inputs />
	</PrecedenceConstraints>
</ExecuteSQL>  

 

We can define tasks in any order, but we need to be aware that a linear constraint will assume a sequential execution order.

Other Properties of the Constraint

Perhaps the most important property (when dealing with multiple input constraints) are logical conditions. That is, whether all or only one inputs need to be completed successfully or not (that is the prior task(s) complete with success or failure.

 

We can set the logical conditions through the tasks <PrecedenceConstraints> tag by using the property LogicalType. This property has two allowable values (And, Or) and specifies that all input constraints must execute prior to this task (if And is specified) or only one prior task must complete before this one (if Or is specified). We can see the outcome of the Or Condition below. Of course there is no need to specify this explicitly, by default AND is used.

 

<ExecuteSQL Name="Select 3" ConnectionName="DB_Src">
  <DirectInput>Select 3</DirectInput>
  <PrecedenceConstraints LogicalType="Or">
	<Inputs>
	  <Input OutputPathName="Select 1.Output"  />
	  <Input OutputPathName="Select 4.Output" />
	</Inputs>
  </PrecedenceConstraints>
</ExecuteSQL>

 

Finally, we may want to specify a failure condition, for example, [Select 3] would only be fired if [Select 4] fails and [Select 1] is successful. This type of control is usually used for error handling. We can do this by specifying the property ExecutionValue property for the constraint. Note that this snippet assumes And (default) logic for constrains

 

<ExecuteSQL Name="Select 3" ConnectionName="DB_Src">
  <DirectInput>Select 3</DirectInput>
  <PrecedenceConstraints>
	<Inputs>
	  <Input OutputPathName="Select 1.Output"   />
	  <Input OutputPathName="Select 4.Output" EvaluationValue="Failure"/>
	</Inputs>
  </PrecedenceConstraints>
</ExecuteSQL>

 

Conclusion

 

There is a high degree of control for managing the execution order of tasks. These can be initially handled with minimum effort by utilising default values for package properties (that is the packages ConstraintMode). Additionally this to full behaviour can be overwritten by explicitly specifying the constraints for each task.

 

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