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 relative database table. Memory is commonly used with an output step, so this is useful if no output is required by a task and data only needs to be written back to a database.

This can be achieved by using a blank script within a Run VB Script step. A recordset or XML output can be consumed.

This example will use a basic task with 3 steps and will show how the date and time can be written into a row of a database table by using external memory.

  • The task runs based on a schedule, a database query is taken and data specified by the user is written back into the database.
    1

Adding the VB Script Step

  1. From the task browser under Execute tools drag and drop the Run VB Script tool into the task planner. This will automatically open the General tab.
  2. Provide an appropriate name and description for the step.
  3. Tick the box Use a Recordset and select a recordset produced by an existing task step, for example a Database Query or Convert XML to Recordset step.
    Capture1
  4. Select the Main tab. Normally script would be entered here but it will be left blank.
  5. Select the Memory button located towards the lower right.
    Capture2This 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.4
  6. Select the Add button and from the list select either OLEDB Memory Provider, Call Stored Procedure Memory Provider or ODBC 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).

Capture3

Key – In the picture above note how the field highlighted has a tick against it. 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. Memory now knows to locate a row based on the key column so data can be mapped to other columns accordingly.

Note: The Key column is not present for Call Stored Procedure Memory.

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. Note: The Test button is used to test if the script is working, it will make real time amendments to the database.

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