Intermediate SSIS Workflow Management – Level 7 of the Stairway to Integration Services

This is the sixth article in a series entitled Stairway to Integration Services. Previous articles in the series include:

Introduction

In the previous installment, we built a new SSIS package, took a first look at scripting and precedence constraints in SQL Server Integration Services, and examined the MaxConcurrentExecutables package property.

In this article we will examine, demonstrate, and test “On Success”, “On Completion”, and “On Failure” functionality of precedence constraints.

Constraint Evaluation

If you have been playing along at home, open the My_First_SSIS_Project solution and the Precedence.dtsx SSIS package. Connect a precedence constraint from Script Task 1 to Script Task 2 as shown in Figure 1:

Figure 1

A Quick Review

Near the end of Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services I included this important blurb:

The semantics here are important. The precedence constraint originates at Script Task 1 and terminates at Script Task 2. The originating task connects to the precedence constraint startpoint which has the “half-bubble” where it connects to the originating task. The endpoint has an arrow where it connects to the terminating task.

Precedence constraints evaluate. They test one or more conditions and, if the conditions are true, the constraint allows the terminating task to start execution.

On Success

To demonstrate, execute the package in the Business Intelligence Development Studio (BIDS) debugger. A message box from Script Task 1 appears first as shown in Figure 2:

Figure 2

When you click the OK button on the message box, Script Task 1 succeeds – turning green to indicate success – and the precedence constraint between Script Task 1 and Script Task 2 evaluates to True. When the precedent constraint evaluates, Script Task 2 executes and displays a message box as shown in Figure 3:

Figure 3

When you click the OK button on the Script Task 2 message box, the Precedence.dtsx package completes and succeeds as shown in Figure 4:

Figure 4

The precedence constraint between Script Task 1 and Script Task 2 evaluates because it is configured to evaluate “On Success” and Script Task 1 succeeded.

Stop the debugger.

On Completion (Success)

Right-click the precedence constraint to see configuration options available on the context menu. At the top we find an “Edit…” option followed by Success, Failure, and Completed as show in Figure 5:

Figure 5

If you click Completion, the precedence constraint changes color, from green to blue as show in Figure 6:

Figure 6

The precedence constraint between Script Task 1 and Script Task 2 is now configured to evaluate “On Completion”. It no longer matters whether Script Task 1 succeeds or fails. As long as Script Task 1 completes execution, the precedence constraint will evaluate and Script Task 2 will execute.

Execute the package in the SSIS debugger to test. Once Script Task 1 succeeds, Script Task 2 will execute as shown in Figure 7:

Figure 7

Stop the debugger.

I can hear you thinking: “Wait a minute, Andy. This only tested ‘On Completion’ precedence with Success. How do we know it works on Failure?” That is an excellent question. The answer is, “you do not know that ‘On Completion’ work on Failure… yet.” So let’s test that.

How do we test “On Failure” execution?

On Completion (Failure)

First, we need a way to generate a failure. Open the Script Task 1 Editor and click the “Edit Script” button. Did you know message boxes can return values? They return integer values that represent the type of button pressed to acknowledge and close the message box. To implement this functionality, change the script contained in Public Sub Main() to the following VB code:

Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim iResponse As Integer = MsgBox("Succeed " & _
         sTaskName & "?", MsgBoxStyle.YesNo, sTaskName)
        If iResponse = MsgBoxResult.Yes Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub

The second and third lines of code are really a single statement concatenated by the “& _” at the end of the first line. The statement declares the variable iResponse as an Integer and sets it to the value returned from the MsgBox function. The MsgBox function returns a value that corresponds to the message box button pressed by the user. The If-Then-Else conditional on the fifth through ninth lines of code respond to the iResponse Integer value; instructing the Script Task to return Success if a Yes button is pressed (MsgBoxResult.Yes), otherwise return Failure.

Close the VB Script editor and then click the OK button to close the Script Task Editor. Execute the package in the BIDS debugger. If you receive no error, your first message box should display as shown in Figure 8:

Figure 8

If you click the “Yes” button, the On Completion precedence constraint will evaluate and Script Task 2 will execute. But this is the use case we demonstrated earlier. If you click the “No” button, Script Task 2 will also execute as shown in Figure 9:

Figure 9

If you followed my instructions precisely during the previous article and this article, there is a possibility you will not see the message box shown in Figure 9 for Script Task 2; but instead see a message box that looks remarkably similar to the new message box we coded for Script Task 1. Why? If you recall, we copied and pasted Script Task 1 to create Script Task 2. Sometimes, enough of the metadata that identifies a copied Script Task will cause code from one script task to execute in the context of a copied and pasted second script task. It is rare, but it happens. To achieve the desired results, open the editor for Script Task 2 and edit its script by adding a line feed somewhere (where you add the line feed really isn’t important). This change will cause BIDS to “re-evaluate” the script embedded in Script Task 2. The script task should then perform as designed.

Now that we know “On Completion” works whether the preceding task succeeds or fails, let’s retest “On Success”!

Retesting On Success

Right-click the precedence constraint and set it to Success. It should change color from blue to green as shown in Figure 10:

Figure 10

When prompted to succeed Script Task 1 click the “No” button to cause it to fail. As shown in Figure 11, Script Task 2 should not execute:

Figure 11

We can use the “On Success” precedence constraint to stop execution if an exception or error occurs. Cool. Also, our snappy Script Task 1 code will allow us to test “On Failure” conditions. Let’s do that now!

On Failure

Stop the debugger and right-click the precedence constraint and select “Failure” as shown in Figure 12:

Figure 12

The precedence constraint will change color to red to indicate it is configured to evaluate on the failure of the preceding task as shown in Figure 13:

Figure 13

Execute the package in the BIDS debugger. When prompted to succeed Script Task 1, click the “No” button. Script Task 2 should execute as shown in Figure 14:

Figure 14

Click the Restart button, shown in Figure 15:

Figure 15

When the package restarts and the Script Task 1 prompt displays, click the “Yes” button to succeed Script Task 1. The precedence constraint, configured to evaluate “On Failure” of Script Task 1, does not evaluate and Script Task 2 does not execute as shown in Figure 16:

Figure 16

Stop the debugger.

Conclusion

In this article, we used SSIS Script Tasks to implement test states for various use cases. We then used the test states to demonstrate how SSIS precedence constraints respond to Success, Completion, and Failure conditions in preceding tasks.

In the next article, we tackle advanced SSIS workflow management using precedence constraints.

Intermediate SSIS Workflow Management – Level 7 of the Stairway to Integration Services

Introduction

Constraint Evaluation