Running a Workflow with a Conditional Path

From OpsWise Documentation Wiki

Open as PDF

In this exercise, we will create a short workflow of SQL tasks. We will begin with a two minute Sleep task so that we will have enough time to see what the workflow looks like on the Activity display when we launch it. We will also create a conditional path, as follows:

• The workflow runs seven days a week and creates a new database table. If that is successful, additional SQL tasks run that insert a value, select a count, and delete a value. Each subsequent task runs if the previous is successful.
• If the first (table creation) task fails, the workflow goes to a Manual task instead of the regular flow. This is the conditional path. The Manual task creates a pause in the workflow and sends an email notification. A user is expected to check the database and fix the problem that caused the first task to fail. If the Manual task is set to a Complete status, it goes to Success and the workflow then returns to the remaining SQL tasks. While the Manual task remains in the Action Required status, the downstream tasks have a Waiting status.

We will also add an email notification and a runbook note to this workflow.

Prerequisite: Since we are using SQL tasks in this exercise, you will first need to create a SQL database connection.

Contents 1 Create a Sleep Task 2 Creating SQL Tasks 3 Creating a Manual Task 4 Creating the Workflow 5 Run the Workflow to Success 6 Run the Workflow Down the Conditional Path

Create a Sleep Task

We will add a Sleep task at the beginning of our workflow so that we will have a chance to view it when the system loads it into the Activity screen.

Create a Sleep task called Sleep 2 Minutes with a Sleep Time of 120 seconds.

Creating SQL Tasks

In this exercise, we will create SQL tasks that execute the following SQL commands:

• Create a new table in the database
• Insert a value into the table
• Select a count value from the table
• Delete the value from the table

Follow these steps to create the SQL tasks:

1. From the navigation pane, select Tasks > SQL Tasks and click New.
2. In the Task Name field, type SQL Create Table.
3. In the Database Connection field, select the database connection you created as a prerequisite.
4. In the SQL Command field, type or cut and paste the following:

CREATE TABLE opswise_tut${_date("yyyyMMdd",5)} (name varchar(128), value varchar(128));




5. Click Submit.
6. Create a SQL task called SQL Insert Value with this command:

INSERT INTO opswise_tut${_date("yyyyMMdd",5)} (name, value) values ('A', 'F'), ('B', 'S'), ('C', 'F');

7. Create a SQL task called SQL Select Count with this command:

SELECT count(*) as count FROM opswise_tut${_date("yyyyMMdd",5)} WHERE value = 'F';

8. Create the last SQL task called SQL Delete with this command:

DELETE FROM opswise_tut${_date("yyyyMMdd",5)};

Creating a Manual Task

A Manual task is used within a workflow to create a pause in processing, during which the user must perform some task. When the user task is complete, the user sets the Manual task to a completed state and processing continues. For our Manual task, we are also going to request a warning if the user takes too long to complete it.

1. Select Tasks > Manual Tasks and click New.
2. Give it the name Pause for Manual.
3. In the summary field, we are going to use another variable that indicates the date and time the Manual task launches:

A Manual task run at ${_date()}

4. Enable the Late Finish field and select the following values:
   • Late Finish Type = Duration
   • Late Finish Duration = Days 00 Hours 00 02 00 (2 minutes)
5. Click the down-arrow and select Save.

Your task should look like this:



6. Now we are going to add a Runbook Note:
   1. Click the Notes tab and click New. The Notes screen appears.
   2. In the Title field, type:

   Probable database problem
   

   3. In the Text field, type:

   Make sure database is running.
   

   
   

   4. Click Submit.
7. Now we are going to add an Email Notification:
   1. Click the Actions tab and click New. You are prompted to select Abort Action, Email Notification, or Set Variable.
   2. Click Email Notification.
   3. Select the status ACTION REQUIRED.
   4. For Email Connection, select the Email Connection you created earlier.
   5. In the To field, type your email address.
   6. In the Subject field, type Issue with Bigger Workflow.
   7. In the Body field, type

   ${_date}  workflow failure; notification triggered by ${ops_task_name}
   

   
   Note: For detailed descriptions, see Creating Email Notifications.

   8. Click Submit.

Creating the Workflow

In this exercise, we will use the SQL, Manual, and Sleep tasks we have already created.

1. Select Tasks > Workflow Tasks and click New.
2. Give it the name Bigger Workflow and click down-arrow > Save to save it.
3. Click Edit Workflow.
4. Use the Add Task tool to drag the tasks you just created onto the canvas:
• Sleep 2 Minutes
• Pause for Manual
• SQL Create Table
• SQL Delete
• SQL Insert Value
• SQL Select Count
5. Organize the tasks as shown in the illustration below.
6. Create the connections shown in the illustration below. If you don't remember how, review the Creating a Simple Workflow exercise.
7. The Success connectors tell the system that if SQL Create Table goes to Success, run Insert SQL Value and the rest of the tasks. Now we are going to create a conditional path that says if SQL Create Table fails, the system should run the Pause for Manual task:
   1. Right-click the connector between SQL Create Table and Pause for Manual.
   2. When the menu pops up, select Conditions.
   3. Enable Failure.
   4. Click OK. Note that the connector is a dotted line, which indicates a Failure connection.
8. On the toolbar, click Save .

Run the Workflow to Success

We are going to launch our workflow and view it from two different perspectives: from the Activity Screen view and the workflow monitor (the canvas).

1. Launch the workflow manually.
2. Display the Activity screen. Because the Sleep 2 Minutes task is still running, your display should look something like this:



3. To view the running workflow from the workflow monitor, click on the Workflow task.



4. Once the Sleep task finishes, the SQL tasks execute. All tasks go to Success and the workflow goes to Success. The only task that did not run is the conditional task, Pause for Manual.

Run the Workflow Down the Conditional Path

Recall that we inserted a date variable into the INSERT TABLE command. Thus, you can run this workflow every day and get a new table name each day, based on the date. For the purposes of our exercise, assuming you are performing it on the same day you did the previous exercise, the SQL Create Table task will fail this time because the table already exists.

1. Return to the Bigger Workflow task and launch it again.
2. From the Activity screen, click the Workflow name to view it from the canvas. This time, the workflow goes down the conditional path. Note that when you set up a conditional path, what would normally be a Failure status for the SQL Create Table task becomes a status of Finished. If you ran this task as a standalone task or without the conditional path, its status would say Failed.



3. When the Pause for Manual task launches, it generates the Email Notification we added earlier.



4. The user receives the email, which provides the name of the task that generated it (Pause for Manual in our case). The user might also be running a special Activity screen that displays only Manual tasks in the Action Required status. According to our scenario, the user opens the Pause for Manual task and checks the Runbook Notes to find out what action he or she is supposed to take. In our case, the Runbook Notes say to check the database and bring it back up.
5. At this point, the workflow processing could continue in one of two ways: 1) Re-run the task that failed and send the workflow down the success path or 2) set the Pause for Manual task to Completed status and continue the workflow from there. We will try both methods.
   • Re-run the failed task. In a real processing situation, this is the method you would use because you need to create the table before you can continue.
   1. Right-click the task that failed and select Commands > Re-run.

   

   2. Note that we are still on the same day so the task fails again because the table already exists. In a real processing situation, the task would go to Success and the subsequent tasks would run as expected.
   • Set the Manual task to Complete status. For the purposes of our exercise, we will get the workflow going again by setting the Manual task to complete.
   1. Right-click on the Manual task.
   2. Select Commands > Set Completed.

   

   3. The Manual task goes to Success and the rest of the workflow runs.

   

   4. Because we set a two-minute Late Finish flag on our Manual task and we (presumably) took more than two minutes to complete it, the Manual task has been flagged as a late finish. To view the flag:
      1. From the Activity screen, click the Manual task name.
      2. The details of the task display. Note that an enabled Finished Late field now displays. The Duration field indicates the duration of the task.

      

   
   

   

   For additional information, see:
   

   • Activity Screen (user documentation)
   • Monitoring Workflows (user documentation)
   • Database Connection (user documentation)
   • SQL Task (user documentation)
   • Manual Task (user documentation)
   • Creating Email Notifications (user documentation)
   • Runbook Notes (user documentation)
   • Creating Conditional Paths (user documentation)
   • Adding Skip/Run Criteria (user documentation)