Datasets

Like variables, datasets are used to represent data in a task that may be different each time a task runs. But unlike standard variables, datasets can contain multiple rows and columns. This is useful when retrieving data that describes an object or objects, or that represents a collection of data such as a database or spreadsheet.

Conceptually, the data in a dataset is laid out like a table and is accessed in much the same way (i.e., by specifying the column and row where the data resides). Several actions in AWE create and populate datasets, including SQL Query, SNMP Get, Get Email actions, and many others.

Using Datasets

Every dataset created and used must have a unique name, much like variables. But because datasets are more like tables (as opposed to variables, which can be thought of more like containers that hold one value), they are referenced differently than other objects. When using datasets, the column name must be specified using the format: %datasetname.columnname%. Typically, one references a value of the current row at a given column (i.e., a column named "address") using an expression:

%MyDataset.Address%

When a dataset is created, the current row is automatically set to 1 (assuming that the dataset has any data, since it is possible for a dataset to have 0 rows, such as when a SQL Query returns no data). A dataset is of minimal use, however, unless one can access the other rows. Typically this is accomplished by using the Loop Dataset action.

The Loop Dataset action takes a dataset name as a parameter and automatically increments the current row with each iteration. The loop continues until all the rows have been accessed. In this way, one could make an AWE task that performs operations on each row of the dataset while using the same expression.

Using a 'Loop Dataset' step is not the only way to access dataset rows, however. It is possible to directly access a particular row of a dataset by supplying the row number within the expression. For example, if the dataset contains five rows and you need to get the address of row 2, use this expression:

%MyDataset(2).Address%

In fact, AWE allows the previous expression (i.e., %MyDataset.Address%) by assuming you mean the current row if an index is not specified.

Dataset Fields

Most of the columns that are returned in a dataset are dictated by the action creating and populating the dataset. For example, when using the SQL Query action, the column names are controlled by the columns returned by the query. In the SNMP Get action, the column names are related to the SNMP information being returned. However, all datasets have a common set of columns that can be used to work with the dataset. These columns can be accessed the same way as other columns, though some of their values are read only. Also, the values of these columns are the same regardless of the row being accessed.

The Datasets folder in the Expression Builder is only populated if you've created datasets, and only after you have run the action at least once. Only then can the Task Builder decide what type of data will be contained in the dataset.

For example, if you create a dataset in the FTP action:

Then run the action in the Task Builder:

Then the folder is populated in the Expression Builder:

Common Name Description

The table below describes the common set of columns that a dataset creates (assuming the dataset name assigned was theDataset).

Exclusively for the If File Exists Actions, the following columns are created (assuming the dataset name assigned was theDataset):

Example using SQL Query action

The SQL Query action is an example of an action that creates and populates a dataset. The fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';

Then the following data set would be generated:

Then the following dataset would be generated (where datasetname specifies the name of the dataset):

datasetname.firstname

datasetname.lastname

datasetname.company

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

%datasetname.firstname%

or you could combine two fields together like this:

%datasetname.firstname + " " + datasetname.lastname%

Embedded Expressions such as these can be used in any parameter in any action.

At runtime the text %datasetname.firstname% is replaced by the contents of the subject of the current record.

The percent signs (%) at the beginning and end of the variable name tell AWE that the text between the percent signs is an expression and should not be taken literally. Instead, it is replaced with the current contents of that column in the current row at runtime.

Example

(To use these examples, copy and paste the text below into the Task Builder.)

<!--- Demonstrates using SQL query with Loop Dataset. Gets all customer where city field is San Diego. Then writes names to a file, to make this task work change the SQL Query connection string and query to match your database--->
<AMSQLQUERY CONNECTIONSTRING="19y9I4LjuL6U1z5kWV9qNz7f2GrLdS16HzZ2IABihh5oydIvZvKxirNVsXfr02s5+YSmraH478OnG3fyNDrnr4k2lmJKCFqoQ/poQgCblLO4gyfnpETTF3SS5ARqugxsajhx7hYL0OypO7YoBsI0EMMvOE56AM64O" RESULTDATASET=" myresults">SELECT firstname, lastname from customer where city='San Diego';</AMSQLQUERY>
<AMLOOPDATASET DATASET="myresults">
<AMFILEWRITE FILE="c:\ customerlist.txt">%myresults.firstname% %myresults.lastname%</AMFILEWRITE>
</AMLOOP>

<!--- This example checks the pop3 mailbox and if a message contains the subject 'Get out of Debt' then it is deleted. To make this task work - modify both POP3 steps with the proper server and account information --->
<AMPOP3MAIL RESULTDATASET="themessages" SERVER="mail.server.com" USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
<AMLOOPDATASET DATASET="themessages">
<AMIFCONTAINS TEXT="%themessages.Subject%" SUBSTRING="get out of debt">
<AMPOP3MAIL ACTION="remove_message" MESSAGE="%themessages.currentrow%" RESULTDATASET="themessages" SERVER="mail.server.com" USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
</AMIF>
</AMLOOP>

See Also

SQL Query

Get Email

Variables