Datasets in Event Rules
You can create and interact with a dataset using Event Rules dataset Actions.
The data in a dataset is laid out like a database table, which has a unique name and consists of columns and rows. The columns consist of pre-defined units of data. The rows contain the actual data for the columns. An example of a simple dataset containing customer data is illustrated below. The name of the dataset is Customers. The first row (in bold) contains the unique names of the dataset columns, which in this case, describes the data type. All other rows include the actual data as described by each column.
Datasets are accessed in the same way that you access information in a database, by specifying the column and row where the data resides. Every dataset created and used must have a unique name. The unique name of the dataset must be referenced followed by the column name enclosed in percentage signs. For example: %DatasetName.ColumnName%
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 through Dataset action, which 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, you could make a task that performs operations on each row of the dataset while using the same expression. For example: %Customers.Email%.
Using a Loop through Dataset Action is not the only way to access dataset rows. 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 data in row 2, simply enter the row number enclosed in parenthesis directly after the dataset name. For example: %DatasetName(2).ColumnName%.
Example of creating and using a dataset in Event Rules
Create an Event Rule to connect to a remote EFT, login as that remote user, grab the contents of the user's home directory, and pipe it out to a dataset. You can list to the dataset over any protocol, HTTPS, SFTP, FTPS
You can take the contents of that dataset and output the contents via Email, Windows Event Log, or write to a file.
Below is an example of what contents you need to put in the Email, WEL, or file write to grab the contents:
Total Rows = %rlisting.TotalRows% Total Columns = %rlisting.TotalColumns% Current Row = %rlisting.CurrentRow% Column Names = %rlisting.ColumnNames% %rlisting.CurrentRow.FileName% %rlisting.CurrentRow.FileSize% %rlisting.CurrentRow.FileDate% %rlisting.CurrentRow.FileType%
Local listing to dataset
Below is an example of what contents you need to put in the Email, WEL, or file write to grab the contents of the local listing:
Total Rows = %locallisting.TotalRows% Total Columns = %locallisting.TotalColumns% Current Row = %locallisting.CurrentRow% Column Names = %locallisting.ColumnNames% Full Name = %locallisting.CurrentRow.FullName% Parent = %locallisting.CurrentRow.Parent% File Name = %locallisting.CurrentRow.FileName% Extension = %locallisting.CurrentRow.Extension% Is Read Only = %locallisting.CurrentRow.IsReadOnly% Creation Time = %locallisting.CurrentRow.CreationTime% Last Access Time = %locallisting.CurrentRow.LastAccessTime% Last Write Time = %locallisting.CurrentRow.LastWriteTime% Attributes = %locallisting.CurrentRow.Attributes% Is Directory = %locallisting.CurrentRow.IsDirectory%
You can specify to loop through the whole dataset, or start and end on a specific row, and specify the column for ordering.
You can specify the order of the loop to start from its Original order, or specify an Ascending or Descending order.
Loops can also be added into other loops
In this event rule, for each cycle of the external loop it will perform an internal loop:
Break from loop
When conditions in the loop are met, there is no need to continue parsing the dataset. In this event rule, the second loop will break after it runs through the dataset one time. The first loop will continue until complete.
CSV Export/Import to Dataset to/from Path
You can import data from a CSV file into a dataset, so that you can later import that work with this dataset in EFT's rules (loop through dataset for example)
In the CSV import to dataset dialog box is used to specify the source CSV file (Click the file icon and click the file that you want to import). In this way, you can create and populate a dataset. Advanced options allow you to treat the first line of data as column headers and specify the delimiter format (comma, semicolon, tab, space, or custom). Comma is the default.
If the option "Treat first line of data as column headers"
is not selected, the dot notation for referencing the fields in a records
will be: Column0, Column1, Column2, and so on.
For the record below it would be %Customer.CurrentRow.Column0%.
You can create an Event Rule that reads in a CSV-formatted file and then uses the records in the dataset in the steps of the Event Rule:
Referencing the dataset in dot notation will look like this in the Send notification email Action:
PowerShell does not handle the % sign. The example below shows how to reference custom variables:
