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:
|
Step 5 |
Create a SQL task called SQL Select Count with this value:
|
Step 6 |
Create a SQL task called SQL Delete with this value:
|
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: |
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:
|
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:
|
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 |
|
---|---|
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. |
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:
|
For additional information, see: