Where data is being uploaded from one system to another, the source data must match the destination's expected formats — for example, where the destination system is expecting values of "UK" but the data source provides values of "United Kingdom". Lookups provide "translation services" for such scenarios.

Such lookups can be held in an external data source, such as the destination database or an independent data source — you use the External Lookup function to retrieve the lookups to successfully provide the translations between the source and destination systems. External Lookup functions are available as part of the Data Transformation Layer (DTLClosed When used as part of a BPA Platform tool, the Data Transformation Layer (DTL) extend the tool's functionality by transforming SML from schema to another. In addition, several transformation functions are available for use with the input data before such data is mapped to the destination schema.) feature.

Adding an External Lookup Function

External Lookup functions are used in the Mapping tab of the following tools:

► Microsoft SQL Server Connector
► Run Microsoft Reporting ServicesClosed The Run Microsoft Reporting Services tool automates the running of Microsoft Reporting Services (MSRS) reports.
► Transform DataClosed Transform Data is a Format tool that enables a new structure to be created from an existing XML or recordset data source. The output generated by this tool is ready for use in a destination system which consumes or requires XML.
► Web Service Connector
  1. Open the relevant task step and go to the Mapping tab.

— Node

— Element

and — Node or element available for mapping

— Mandatory element that must be mapped and included in the output

  1. Click Functions to expand the Functions pane.
  2. Expand the Lookup node and drag External Lookup to the Transformation Mappings pane.
  3. Drag the relevant Input Data source node or element to the new icon.
  4. Do the same for the receiving Output Data node or elements.
  5. Double-click the icon.

The Transformation Mappings pane is replaced with the External Lookup's configuration pane:

Data Transformation Layer - External Lookup

  1. Enter a meaningful Function Name.
  2. The input (source) and output (destination) links created previously now appear in the Inputs and Output tabs. If you have more than one connected input or output, it is recommended you Edit each Parameter in turn and rename it.
  3. To change an existing mapping, either Inputs or Output, use the drop-down lists under Mapped To to select a new node or element.

Specifying the Data Source

The External Lookup function uses existing Database Query (ODBC)Closed You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. or Database Query (OLEDB)Closed You typically use the Database Query (OLEDB) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. global connections; if one doesn't already exist, you must create the connection to the external data source first. If you have an existing connection to the external data source, go to Connecting to the External Data Source.

Creating the External Data Source

You can only connect to your external data source using an ODBC or OLEDB driver; both are accepted by the External Lookup function.

Creating an ODBC External Data Source

You do this in the ODBC Connections interface which sits outside the DTL. You open this interface from the resources tree — expand System > Tools > Data Connectors or Input, and double-click Database Query (ODBC) in the items list.

  1. Click Add to create a new connection and select the required ODBC driver or data source.
  2. Expand ODBC Drivers to see all ODBC drivers available on the client computer.

    Note: Any computer hosting a BPA Platform client that runs tasks using this connection, including the BPA Platform server, must have the selected driver installed on the computer. Without the driver, the task will fail.

  3. Once a driver has been selected, add the connection details to the database. Note that the configuration interface is different for every driver available.

For more information, see Adding ODBC Connections.

Creating an OLEDB External Data Source

You do this in the OLEDB Connections interface which sits outside the DTL.

You open this interface from the resources tree — expand System > Tools > Data Connectors or Input, and double-click in the items list.

Note: It doesn't matter which tool category you select the tool from, the same interface is opened.

  1. Click Add to create a new connection; select the required OLEDB driver or data source.
  2. Expand OLEDB Drivers to see all OLEDB drivers available on the client computer.

    Note: Any computer hosting a BPA Platform client that runs tasks using this connection, including the BPA Platform server, must have the selected driver installed on the computer. Without the driver, the task will fail.

  3. Once a driver has been selected, add the connection details to the database. Note that the configuration interface is different for every driver available.

For more information, see Adding OLEDB Connections.

Connecting to the External Data Source

  1. Reopen the relevant task step and go to the Mapping tab.
  2. Double-click the icon and go to the Data Source tab.
    1. From the Connection drop-down, choose the connection to your external data source.
    2. From the Data Object tree, double-click the table containing the lookups.
  3. Go to the Columns tab.
    1. From the input Mapped Data Source, choose the column containing the values received from the input data source.
    2. From the output Mapped Data Source, choose the column containing the expected values of the destination system.

Data Transformation Layer - External Lookup - Columns Tab

In the example above, when a value is received that matches one listed in the TypeID column, the corresponding value in the TypeName column is sent to the destination.

  1. (Optional) Go to the Query tab. This contains the auto-generated SQL script for querying the lookup table (Data Source and Columns).
    1. Click Test.
    2. In Parameter Value, enter an example value for the input lookup.
    3. Click Execute to check that the returned value is correct.

Selecting Error Handling Options

Use the Options tab to define error handling behaviour for this function.

At task run-time, if a mapped input element is not included in the received input recordset or XML, you can choose to:

► Output an empty String — An empty string is passed to the mapped output node at task run time, for example, <country></country>.
► Output no Data — The element isn't included in the output XML.
► Output Value — Enter a static value in the provided text box; this value is passed to the mapped output at task run-time.
► Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

If the external lookup to the database table returns more than one value for the output parameter, you can choose to:

► Use first value returned — All other records containing the other values are ignored.
► Assign value — All returned values are ignored; instead the value entered here is used.
► Error — The function logs an error to the BPA Platform Event Log. Depending on the parent task step's own error handling options, the task can either continue or fail completely.

Click OK to save the function and return to the parent task step's Mapping tab.