Transform functions – Run VBScript

The data functions in the DTC

Among the pre-defined functions provided in the data transformation component to support data transformation, there are two that are classed as data functions, Fixed/Dynamic Data and Run VB Script.

Here we focus on the Run VB Script function.

Using VB Script in data transformation

The Run VB Script data function allows you to transform source data using the VB Script language.

It runs a script (in VB Script) that uses the source data to calculate values. One or more nodes in the tool input XML schema are then populated with the calculated values.

Many inputs may be consumed and reprocessed by the script, to be mapped to the tool input schema. This provides great flexibility in defining a transformation.

Focus on the Run VB Script function

You access the Run VB Script function from the Data category in the Functions pane of the data transformation component (DTC).

To use the function, you drag an instance of it into the Transformation Mapping area and then drag the required connectors into place.

You can also open the function’s dialog box by double-clicking the function icon and set various options there.

The interface presents the VB Script language reference when viewing or editing the script.

pic1

Using the Run VB Script data function

Let’s look at a typical situation where the Run VB Script function could be applied. In this example, it is being used to provide an MD5 encryption value.

MD5 is a commonly used encryption method for authenticating either a user or an application. It is often made up of a pre-shared secret key, a username, a password and a transaction ID.

We’re not concerned here with the detail within the function, but simply how it is deployed in the DTC.

pic2

Above: you can see the DTC for the Web Service Connector tool, showing the data transformation for our example using the Run VB Script function.

pic4

The above two examples illustrates a situation where the data from the input schema does not match what we need to have passed to the tool input.

With any function, there may be a number of inputs and outputs mapped, in this case three inputs and one output.

Within the function there is some VB Script script which does the data conversion appropriate to the requirements for the tool input schema.

pic5

Above: you can see, in place of the Transformation Mappings pane, the Transformation Function settings are displayed for the Run VB Script function.

In the tables under the Inputs and Output tabs, you can view the input and output parameters that have been mapped in the Transformation Mappings pane, together with their mappings.

pic6

Above: The inputs have been renamed from their default values (of input0, input1 and input2) to Password, SenderID and TransactionID, to make them more meaningful.

pic7

Above: The output has been renamed from the default value (of Output0) to Output.

pic8

You can see above how the mapping is defined by its fully qualified schema element location.

In our example, there is only one output, but a function can support more than one.

pic9

Above: Under the VB Script tab, you can view the VB Script code that carries out the data conversion. Any valid VB Script code can be hosted here. In our example, the function has been documented and bears the name MD5Encrypt.

pic10a

Above: Notice how the Function pane has now changed to reflect the context. It identifies the input and output names used for this function.

pic11-b

Here is a simpler example of the use of the Run VB Script function. Some input strings are being concatenated and a VB Script constant is being used to produce a block address.

The VB Script constant VBCrLF represents “carriage return, line feed”.
It is often used to delineate address lines.

pic12b

In the function pane, the VB Script node is expanded to reveal the VB Script lookup library.

You can see the VBCrLF constant under Constants – String.

There are a lot of useful functions for use in helping to produce any scripting required.

pic13b

Above: Like other DTC functions, the Run VB Script function has the option to set the behaviour for the handling of any exceptions that may occur.

It is done under the Options tab. The default setting is to output an empty string. Other options are to output no data, to output a specified value or trigger an error message.

Run VB Script: In summary

The data functions in the TaskCentre DTC are used to manipulate data in various ways before it is consumed by the tool. TaskCentre provides two data functions, Fixed / Dynamic Data and Run VBScript.

In the Run VB Script function, you make use of the VB Script scripting language to define data transformations. The function uses the script to calculate values from the source data and then populate the tool input schema with those values. This can provide great flexibility in transformations by processing many inputs.

To use the Run VB Script function, you drag an instance of it from the functions pane to the transformation mappings pane and then drag connectors between the function box and the required xml elements in the data input schema or the tool input schema.

You can open the function’s settings and specify input and output parameters and their mappings, set exception behaviour and, under a VB Script tab, view the VB Script which carries out the data transformation. You can expand the VB Script node in the function to view the VB Script lookup library.

Links to related DTC articles