Working with Databases
| Caution |
| Test the script(s), processes and/or data file(s) thoroughly in a test environment, and customize them to meet the requirements of your organization before attempting to use it in a production capacity. (See the legal notice here) |
Note: The workflow sample mentioned in this article can be downloaded from the Opalis project on CodePlex: https://opalis.codeplex.com |
Overview
This is a collection of workflows that demonstrates the use the Query Database and Write to Database Activities to interact with SQL databases.
Getting Started
These example Workflows depend on the Northwind sample database for SQL Server. The Northwind sample database can be downloaded from:
Example Workflows
1. Query Database
In this example we automates following use case:
- Query for the Employee Titles from the Employees Table
- Aggregate the Results
- Count the Number of different Titles
- Sort Descending
- Filter the Results
- Only work with Counts > 5
- Create a New Table based on the Title
- Name format should not include spaces
- Query for the Names from the Employees Table with the Filtered Title
- Name format should be FirstName LastName
- Insert the Names into the Newly Created Table
The workflow for this use case is as follows:
The Custom Start Activity is used to obtain the SQL Server name using a parameter:
The Query Database Activity ‘Get Titles and Count’ runs a query against the Northwind sample database:
The SQL Server name is obtained from the Custom Start Activity by subscribing to the ‘SQL Server’ Published Data:
The Query result is returned as multi-value data with a delimited string for each row returned:
Sales Representative;6
Vice President, Sales;1
NULL;1
Inside Sales Coordinator;1
Sales Manager;1
The Compare Values Activity ‘Filter by Count > 5’ uses a ‘Field’ Data Manipulation Function to evaluate the value for the rows counted for each Title:
The Query Database Activity ‘Format New Table Name’ ensures that the returned Title does not contain any spaces as it will be used as the name of the new table that will be created in the next Activity. This is accomplished by using a combination of SQL and an Opalis Data Manipulation Function:
The Query Database Activity ‘Create New Table’ creates a new table based on the Title returned by the previous Activity:
The Query Database Activity ‘Get Names for Filtered Results’ queries for the Names from the Employees Table with the Filtered Title and ensures the name format is ‘FirstName LastName’:
The Query Database Activity ‘Insert Filtered Records’ inserts the returned data from the previous Activity into the new table:
Running the example Workflow
Launch the Workflow ‘1. Query Database’ by using Start, or by running it using the Testing Console. When prompted, enter the appropriate SQL Server name:
Check the Opalis Designer Events tab or the Testing Console log to see the resulting output.
In SQL Server, the resulting table will have following contents:
More Information
Refer to the Query Database Activity Online Help for more information.
2. Write to Database
This Workflow uses the Custom Start Activity to obtain input data using three parameters:
- SQL Server: the name of the SQL Server to connect to
- First Name: a string representing a first name
- Last Name: a string representing a last name
The Write to Database Activity subscribes to the Published Data variables returned for the ‘First Name’ and ‘Last Name’ parameters to insert a new record into the Northwind database ‘Employees’ table:
The SQL Server name is obtained from the Custom Start Activity by subscribing to the ‘SQL Server’ Published Data:
Running the example Workflow
Launch the Workflow ‘2. Write to Database’ by using Start, or by running it using the Testing Console. When prompted, enter the appropriate values:
Check the Opalis Designer Events tab or the Testing Console log to see the resulting output.
Open the ‘Employee’ table from the Northwind database in SQL to view the newly added record.
More Information
Refer to the Write to Database Activity Online Help for more information.
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Comments
- Anonymous
February 16, 2011
I'm wondering how I setup a connection with a non-deafult port to a SQL server? I have tried the usual ":" What am I doing wrong? Thanks for your help.