SQL Server Integration Services offers vast functional enhancements over DTS. One impressive change is the flexibility of new Precendence Constraints.
Right-clicking a precedence constraint reveals a context menu:
Clicking Edit reveals most of the new functionality. To demonstrate, let's walk through a simple example.
First, add a new variable named Now as shown below:
Drag an Execute SQL Task onto the Control Flow workspace:
Double-click the task to open the editor. Enter some generic SQL in the task as shown:
Change the Resultset fron "None" to "Single row":
Click the Result Set navigation link. Add "Now" as the Result Name and "User::Now" as the Variable Name. This maps the single row returned by the query to the user variable name "Now" - which assigns the current server time to the [User::Now] variable:
Test the package by clicking the Run button (or F5). The package fails with the following error:
Clicking the Execution Results tab reveals the error to be related to the Result Binding:
To correct the error, add an alias ("Now") to the query:
The package now succeeds:
Add two more Execute SQL Tasks. Configure them as shown below. Change the Name property of the second task to "Execute SQL Task 31 - 60":
Drag a new precendence constraint from the first task to the second. Right-click the precedence constraint and select Edit:
Change the Expression operation from "Constraint" to "Expression and Constraint":
Leave the Value set to Success. Enter "DatePart("s", @[User::Now])<=30" in the Expression textbox. Leave the Multiple Constraints option set to Logical AND - this means the previous task must succeed (the Success value must be satisfied) and the Expression must evaluate to True (the seconds of the current minute must be between 0 and 30 inclusive) for this precedence constraint to execute. Click OK to close the precedence constraint editor:
The precendence constraint now displays an fx symbol to indicate it is controlled by an Expression:
Drag another precendence constraint from the first Execute SQL Task to the third (31-60) task. As before, enter "DatePart("s", @[User::Now])>30" in the Expression textbox. Leave the Multiple Constraints option set to Logical AND - this means the previous task must succeed (the Success value must be satisfied) and the Expression must evaluate to True (the seconds of the current minute must be between 31 and 59 inclusive) for this precedence constraint to execute. Click OK to close the precedence constraint editor:
The second precendence constraint now indicates an Expression. Click the Play (F5) button to start execution. Depending on which half of the current minute the execution occurs, one of the two images below will display indicating that precedence constraint's Expression to evaluates True. Clicking Play 30 seconds later will cause the other precedence constraint's Expression to evaluate True:
Precedence Constraints in SSIS provide lots more functionality than their predecessors (no pun intended!).
:{> Andy
Technorati Tags: SSIS Sql Server Precendence Constraint