Learn about: Writing key data to a database using external memory

This example will be using the output step Send Email (SMTP) to demonstrate the use of external memory. However, any output tool can be used as the memory feature is available to all of them.

If you wish to write data to a database but not produce an output then you can use the VB Script tool with an empty script. Please follow the below link for an example of how this would work:

Learn about: Run VBScript – Writing key data to a database using external memory

Overview

Memory is used to store specific data generated by a task, it is generally used for auditing purposes and holding historic data. It can be setup in a way that causes a task to only produce results when certain conditions have been met by utilising the stored data.

External memory is a way of writing data to a database by using TaskCentre data such as recordset columns and/or by the use of variables and formulas. It is the method most commonly used due to its ability to quickly write data from multiple tasks. In comparison, internal memory which uses a TaskCentre repository per task can become a slow process once many repositories have been built up.

A SQL ODBC or OLEDB database system is used as the memory location.

Example: A client exceeds their credit limit and a notification goes out. Memory is used to write to a database table the time and date that the notification was sent. A database query is configured to restrict the re-sending of notifications for the next 7 days based on the data previously written to the table.

Where Memory Is Used

The memory feature is available for use by the following steps:

  • Save File
  • Send Email (SMTP)
  • Transfer File (FTP)
  • Send SMS
  • Send Fax
  • Call COM Object
  • Run External Program
  • Print Document
  • Run VBScript
  • Create Workflow Job

Adding a memory definition to the output step

From within the Main tab of the Send Email (SMTP) tool, memory is added to the task by using the Memory button.

Capture1(1)

This will display the Memory Definitions dialog where memory definitions are created and maintained. A memory definition refers to the type of memory in use by the task, once created it will be listed within this box.

Memory 4

Select Add and from the list select either OLEDB Memory Provider, Call Stored Procedure Memory Provider or ODBC Memory.

Memory 5

Configuring External Memory

It is at this point where a data source is chosen for task generated data to be stored by using the Data Source drop down menu. A database table is selected by using the Browse button after which the columns will display within the Columns table. If using Call Stored Procedure Memory, then the parameters of the stored procedure will be displayed instead.

The task browser is used to drag & drop TaskCentre data into the Value column against the relevant table column or parameter where the data is to be written. A Key is assigned so that the correct row to write to in the table can be identified.

Memory 8

The above screenshot shows a recordset column being used as the key.

Memory 9

Further down the list a formula is being used to write the current date & time to a column in the table.

Name – A unique name for the memory definition is entered here.

ODBC/OLEDB Data Source(drop down list)- This drop down list is used to select a connection to a database.

Table – Clicking on the Browse button enables a table to be selected from the database.

Columns (sub-section):

Name – This displays the name of the column in the table.

Value – The value is what is written into the table. Data is mapped here from the task browser for example recordset columns, fixed/dynamic variables or formulas. By default the field will display (not mapped).

Capture2

Key – When data is to be written to a certain row in a table it needs to have a way of identifying which row is the correct one. This is known as a ‘Key’.

The key needs to be a unique string of information in a column which has a different value per row, for example an account number or the row ID. When selected as the key it is only used as a reference point and cannot have data mapped to it. Once a row has been located based on the key column, data can be mapped to other columns accordingly.

Memory 10

Edit SQL – This button displays the SQL script showing the process of how the table is going to be updated. It is a ‘Free Type Mode’ which allows the user to manually edit the script if required.

Clear – Used to clear anything mapped to the columns, will prompt for a confirmation when selected.

To read about tasks which make use of External Memory see:

Learn about: Tasks which write key data to a database – External memory

Related Articles

Learn about: Writing key data to a database using external memory when no ouput is required

Run VB Script is an Execute tool which is normally used to automate the running of a VB script in a task. Alternatively, it can be used in conjunction with memory to store data generated at task run time and write it to a…

Read More
BPA-8236