Introduction

In this exercise, we will create a short workflow of SQL tasks. We will begin with a two-minute Timer task so that we will have enough time to see what the Workflow looks like on the Activity Monitor 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 successor tasks have a Waiting status.

We will also add an Email Notification and a Note to this Workflow.

Prerequisite

Since we are using SQL tasks in this exercise, you will first need to create a SQL Database Connection.

Create a Timer Task

We will add a Timer task at the beginning of our Workflow so that we will have a chance to view it when Universal Controller loads it into the Activity Monitor.

Step 1

From the Automation Center navigation pane, select Tasks > Timer Tasks. The Timer Tasks list displays.

Step 2

Click New to display an empty Timer Task Details and enter / select the following values:

Step 3

Click the Save button.

Create 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.

Perform the following steps to create the SQL tasks:

Step 1	From the Automation Center navigation pane, select Tasks > SQL Tasks. The SQL Tasks list displays.
Step 2	Click New to display an empty SQL Task Details and enter / select the following values:
Step 3	Click the Save button.
Step 4	Create a SQL task called SQL Insert Value with this value: SQL Command = INSERT INTO opswise_tut${_date("yyyyMMdd",5)} (name, value) values ('A', 'F'), ('B', 'S'), ('C', 'F');
Step 5	Create a SQL task called SQL Select Count with this value: SQL Command = SELECT count (*) as count FROM opswise_tut${_date("yyyyMMdd",5)} WHERE value = 'F';
Step 6	Create a SQL task called SQL Delete with this value: SQL Command = DELETE FROM opswise_tut${_date("yyyyMMdd",5)};

Create 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.

Step 1	From the Automation Center navigation pane, select Tasks > Manual Tasks and click New.
Step 2	In the Manual Task Details, enter / select the following values: `${_date()`}
Step 3	Click the Save button.
Step 4	Add a Note:
Step 5	Add an Email Notification:
Step 6	Click the Save button.

Create a Workflow

Create a Workflow containing the Timer, SQL, and Manual tasks that you just created.

Step 1	From the Automation Center navigation pane, select Tasks > Workflow Tasks and click New.
Step 2	In the Workflow Task Details, enter the following value: Task Name = SQL Workflow
Step 3	Click the Save button, right-click SQL Workflow on the Workflow Tasks list, and then click Edit Workflow on the Action menu.
Step 4	In the Workflow Editor, use the Add Task tool to drag the tasks you just created onto the canvas.
Step 5	Organize the tasks and create connections as shown in the following illustration. The Success connectors tell the Controller that if SQL Create Table goes to Success, run Insert SQL Value and the other SQL tasks.
Step 6	Create a conditional path specifying that if SQL Create Table fails, the Controller should run the Pause for Manual task: Right-click the connector between SQL Create Table and Pause for Manual. On the pop-up menu, click Conditions. On the Conditions pop-up dialog, enable Failure and click the OK button. Note that the connector is a dotted line, which indicates a Failure connection.
Step 7	On the Workflow Editor toolbar, click the Save icon.

Run the Workflow to Success

We are going to launch our Workflow and view it from two different perspectives: from the Activity Monitor and the Workflow Monitor.

Step 1	Launch the workflow manually.
Step 2	Display the Activity Monitor. Because the Two Minute Timer task is still running, your display should look similar to this:
Step 3	Right-click SQL Workflow on the Activity Monitor list to display an Action menu and select Workflow Task Commands > View Workflow. The Workflow Monitor opens and shows progress on the task. The Workflow Monitor updates automatically with each status change. When the Timer 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.

Step 1	Return to the Bigger Workflow task and launch it again.
Step 2	From the Activity Monitor, 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.
Step 3	When the Pause for Manual task launches, it generates the Email Notification we added earlier.
Step 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 Monitor 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 Notes to find out what action he or she is supposed to take. In our case, the Notes say to check the database and bring it back up.
Step 5	At this point, the workflow processing could continue in either of two ways: Re-run the task that failed and send the workflow down the success path. 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; right-click the task and select Commands > Re-run. In a real processing situation, this is the method you would use because you need to create the table before you can continue. Right-click the task that failed and select Commands > Re-run. 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. Right-click the Manual task. Select Commands > Set Completed. The Manual task goes to Success and the rest of the workflow runs. 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, go to the Activity Monitor and click the Manual task name. In the task instance Details, an enabled Finished Late field now displays, and the Duration field indicates the duration of the task.

For additional information, see:

Universal Controller 7.2.x

Tutorial - Running a Workflow with a Conditional Path