Microsoft SQL Server Connector v1.1

Introduction to the Microsoft SQL Server Connector

What is the Microsoft SQL Server Connector?

The Microsoft SQL Server Connector allows you to read, write and query data to Microsoft SQL Server databases. It expands on the existing functionality available with the Database Query and Call Stored Procedure tools, providing access to a nominated SQL Server database and therefore automating management of your data.

All communication uses XML. Recordset data can be mapped to required XML elements directly in the Connector itself without the need for conversion.

Microsoft SQL Server Connector Features

• On-premises and cloud instances supported.
• Control which databases are available at task design and run times.
• Standard SQL commands (CREATE, DELETE, UPDATE) are supported.
• MERGE command support, where child records are updated or added depending on a key being available. This simplifies task design as it removes the need to have a check for existing child records and separate steps for the ADD and UPDATE operations.
• SYNC command support, similar to the MERGE operation however, child records for which no keys are supplied will be removed during the operation.
• Use of lookups to replace a value in a table with the values from another, for example, a foreign key ID with
  the corresponding description from another table.
• Memory support for ODBC, OLEDB, Call Stored Procedure, and internal repositories.
• Combine parent-child tables into a single business object for use in a task step.
• Pass-through fields support, which act in a similar manner to SupplementaryReference — data is passed directly to the step output documents bypassing SQL Server itself.
• Ability to update tables through objects that contain views.
• Password protect (lock / unlock) business objects.
• Within SEARCH operations, specify whether only a selected number or percentage of records are to be
  returned.

The Microsoft SQL Server Connector Tool Pack

The tool pack consists of:

• Microsoft SQL Server Connector Agent — The Agent communicates directly with SQL Server. It can be installed on any computer that has access to both the BPA Platform server and the required SQL Server instance, local to the BPA Platform server, or the server instance hosting the SQL Server database.
• Microsoft SQL Server Connector — The Connector communicates with the Microsoft SQL Server Connector Agent instead of interacting directly with the SQL Server database. It must be installed on the BPA Platform server and any remote BPA Platform client machines which run Microsoft SQL Server Connector tasks.

White Paper - Microsoft SQL Server Connector v1.1

The Microsoft SQL Server Connector expands on the existing functionality available with the Database Query and Call Stored Procedure tools, providing access to a nominated SQL Server database and therefore automating management of your data.

Download Brochure

System Requirements

The following prerequisite software must be in place before installing the Microsoft SQL Server Connector.

Minimum BPA Platform Version

The Microsoft SQL Server Connector requires BPA Platform 2020 Update 1 or above.

Minimum SQL Server Version

The following versions of SQL Server are supported:

• Microsoft SQL Server 2012
• Microsoft SQL Server 2014
• Microsoft SQL Server 2016
• Microsoft SQL Server 2017
• Microsoft SQL Server 2019

Note that the following SQL Server platforms are also supported:

• On-premise
• Cloud
• Azure

Architecture

The diagrams below provide high-level architectural overviews of the Microsoft SQL Server Connector with BPA Platform and an SQL Server database in the three main SQL Server deployment environments.

The Microsoft SQL Server Connector Agent must be installed on any computer that has access to both the BPA Platform server and the required instance of SQL Server — installing directly onto the BPA Platform server or the computer hosting the SQL Server instance is supported.

The Microsoft SQL Server Connector must be installed onto the BPA Platform server and any remote BPA Platform clients that run Microsoft SQL Server Connector tasks.

Example On-Premise Architecture

For ease of viewing, a single Microsoft SQL Server Connector Agent is shown connecting to all required components — this is a valid architecture. But if required, you can install a separate Agent for each instance of SQL Server that your organisation makes use of.

Example Cloud Architecture

For ease of viewing, a single Microsoft SQL Server Connector Agent is shown connecting to all required components — this is a valid architecture. But if required, you can install a separate Agent for each instance of SQL Server that your organisation makes use of — note that the Agent must be installed on a physical computer, installation into the SQL Server cloud instance is not supported.

Example Hybrid Architecture

For ease of viewing, a single Microsoft SQL Server Connector Agent is shown connecting to all required components — this is a valid architecture. But if required, you can install a separate Agent for each instance of SQL Server that your organisation makes use of — note that the Agent must be installed on a physical computer, installation into the SQL Server cloud instance is not supported.

Working with Other Tools

Consuming XML from Other Tools

The Microsoft SQL Server Connector can consume output from the following tools:

Icon	Tool Name	Tool Category
	Call Stored Procedure (OLEDB)	Input, Data Connectors, Output, and Execute
	Database Query (ODBC)	Input and Data Connectors
	Database Query (OLEDB)	Input and Data Connectors
	Import Flat File	Input
	Import XML Document	Input
	Retrieve Text Message	Input
	Convert Recordset to XML	Format
	Convert XML to Recordset	Format
	Transform Data	Format
	Call Task	Execute
	Applications Platform Connector	Data Connectors
	Microsoft SQL Server Connector	Data Connectors

In addition, the Microsoft SQL Server Connector can consume the output from other Data Connector tools that provide connectivity to an external application, such as an eCommerce, ERP or CRM system.

Objects Consumed

The Microsoft SQL Server Connector consumes the following objects exposed by other steps:

• Recordset — Tabular data from any BPA Platform tool capable of exposing such data (see above)
• XML — XML data from any BPA Platform tool capable of exposing such data (see above)

Exposing XML to Other Tools

The following tools can consume the XML outputted by the Microsoft SQL Server Connector:

Icon	Tool Name	Tool Category
	Retrieve Text Message	Input
	Convert XML to Recordset	Format
	Run Microsoft Reporting Services	Format
	Save File	Output
	Call Task	Execute
	Applications Platform Connector	Data Connectors
	Web Service Connector	Data Connectors
	Microsoft SQL Server Connector	Data Connectors

In addition, other Data Connector tools that provide connectivity to an external application, such as an eCommerce, ERP or CRM system, can also consume the output from the Microsoft SQL Server Connector.

Objects Exposed

The Microsoft SQL Server Connector tool outputs the following objects which can be consumed by other tools:

• InputData — The InputData node is only available when another task step provides the consumed Data Source. When consuming a recordset, this node lists the recordset columns; for
  XML, it contains the XML document received by the Microsoft SQL Server Connector.
• MemoryDefinitions — If Memory is used in the task step, MemoryDefinitions contains the objects parsed.
• OutputData — The OutputData object contains two sub-objects:
  • XmlString — This is the XML document produced by the tool, containing data returned from SQL Server. Also included are the key fields for the mapped elements affected by the used operation and a SupplementaryReference field for task auditing purposes.
    The mapped fields in the Mapping tab (see About the Mapping Tab) define the structure of this XML document.
  • XmlSchema — This contains the output schema in XSD format.
• ErrorData — The ErrorData object also contains two sub-objects:
  • XmlString — This contains any error data reported by SQL Server
    <Error> — All errors are created as an <Error> node, with the following sub-nodes:
    <Object /> — The name of the requested object
    <CODE /> — The error code returned by SQL Server
    <MESSAGE /> — The corresponding error message
    <EXTENDEDINFO /> — A string containing additional information about the error
    <INPUTDATA /> — The header input data (excluding child nodes) mapped for the object, plus all data contained in SupplementaryReference
    </Error>

    For more information about the errors received, see Error Handling.

  • XmlSchema — This contains the output schema in XSD format.
• ResponseData — The ReponseData objects contains two sub-objects:
  • XmlString — This contains the XML document containing the data received back from SQL Server.
  • XmlSchema — This contains the output schema in XSD format.
• RequestObject — This object exposes all the selected object and operation’s fields available, irrespective of whether they are part of the mapped structure, and the data sent to SQL Server.
• ResponseObject — This object exposes all the selected object and operation’s fields available, irrespective of whether they are part of the mapped structure, and the data received from SQL Server.
• Step Properties — Standard step properties are also available allowing you to use statistical data of the Microsoft SQL Server Connector step.

Where Can the XML Output be Used?

The incoming XML is translated into the XML format for the object and operation selected in the configuration. The data for the linked fields is brought across into the output XML — only those fields that are linked are brought across. The XML is passed to the Connector, which then:

• Processes the data
• Performs the operation requested
• Receives an XML document containing the response

Both the OutputData and ErrorData documents can be directly used by succeeding task steps that can consume XML data, as part of an application integration or synchronisation process. To use the documents in a non-XML consuming tool, use a Convert XML to Recordset step first to create a recordset copy of the XML data. The XML documents are also available as consumable objects from the Task Browser (XmlString). When used in a task step, such as Format as Text or Save File, this exposes the actual XML string.

Error Handling

Errors are written to the BPA Platform Event Log (Tasks toolbar > Event Log ). You define how errors are handled in the Options tab of the tool (see About the Options Tab).

Reasons for the errors could include:

• Web service connection errors
• User privilege errors
• Errors, messages, and warnings from SQL Server
• Any reported task runtime errors, including Agent errors, such as, loss of connection

Global Configuration

The global configuration for the Microsoft SQL Server Connector is used to create connections to the Microsoft SQL Server Connector Agent, and from the Agent to the SQL Server database.

You open this interface from the resources tree — expand System > Tools > Data Connectors and double-click Microsoft SQL Server Connector in the items list.

When a connection is created, the required business objects (tables) of your SQL Server instance are retrieved.

Click Add to create a connection to SQL Server.

Using Extended Logging

Selecting this option exposes the full XML parsed between the Microsoft SQL Server Connector and a SQL Server database.

Without extended logging, the Event Log only contains start and end of transaction messages, plus any error messages encountered at runtime.

You can view the extended log in the BPA Platform Event Log (Tasks toolbar > Event Log).

About the General Node

You must name each connection you create to a SQL Server database.

Provide a unique Connection Name. If your SQL Server instance makes use of a number of separate databases, it is recommended you add such details to the name. This is useful later when creating task steps.

Updating Objects and Operations

Enabling Update Objects and Operations allows the SQL Server schema in BPA Platform to be refreshed every time an update is made in the database structure itself, such as a new column added to a table.

About the Agent Connection > General Node

Use the General node to determine which Microsoft SQL Server Connector Agent this global connection is for.

In Connector Agent Server, enter the IP address or hostname of the machine where the Microsoft SQL Server Connector Agent is installed. If the Agent is on the same machine as this instance of BPA Platform, select Use Localhost.

By default, the connection is over HTTP and port 4210. If the Agent has been set up to use HTTPS or a different port, enable Show Advanced Options.

Use Test Connection to ensure the Microsoft SQL Server Connector can communicate to the Agent successfully.

About the Agent Connection > Advanced Node

Use the Advanced node to change the communication defaults for the Microsoft SQL Server Connector Agent this global connection is for. This node is hidden until Show Advanced Options in the Agent Connection > General node is selected.

By default, the connection is over HTTP and port 4210.

To change the port used to communicate over, select Set Custom Port Number and enter the required number in the box provided.

To use HTTPS communication, select Use HTTPS.

About the SQL Server Connection > General Node

Use the General node to determine which SQL Server instance this global connection is for — note that you specify the actual database to connect to in the Database node — see About the SQL Server Connection > Database Node.

Note that the SQL Server Connection node and child nodes are not available until a valid connection to the Microsoft SQL Server Connector Agent is created.

In Server Address, enter the IP address or hostname of the machine where the SQL Server instance is installed. If this is on the same machine as this instance of BPA Platform, select Use Localhost.

Enter the relevant Username and Password for the Microsoft SQL Server Connector Agent to use. If Windows authentication is required, select Use Windows Authentication — note that you are not prompted for details; instead the credentials from the account the Microsoft SQL Server Connector Agent service is logged in as are used. For a detailed description of how to change the service’s user, refer to your Microsoft documentation.

Use Test Connection to ensure the Microsoft SQL Server Connector Agent can communicate to the SQL Server instance successfully.

About the SQL Server Connection > Database Node

Use the Database node to determine which SQL Server database this global connection is for. Note that you must create a separate connection for each database you want to create tasks for, even when those databases reside in the same SQL Server instance.

Choose the relevant database from the SQL Database drop-down. Only databases found in the instance configured on the General tab (see About the SQL Server Connection > General Node) are made available, and a dynamic selection (where the database name is determined by a BPA Platform variable or formula) cannot be used — if required, use the step configuration for this, see About the Connection Tab.

Click Load Cache, this will load tables, columns, and relationships into the Microsoft SQL Server Connector’s memory for faster processing. You must click Refresh Cache should any changes occur to the connected database’s structure to ensure the cache contains the latest tables, columns, and relationships. Additionally, if this connection is changed to point to a different database, you are prompted to refresh the schema.

About the Business Object Designer Tab

Use the Business Object Designer to create business objects that relate to one or more tables in your SQL Server database, effectively reducing the number of tables available during task step design. This is useful when dealing with SQL Server database instances with large schemas.

Note that this tab is only enabled when a successful connection to a SQL Server database is made in the Connection Options tab.

The Business objects pane lists those objects made available for this connection. Selecting an object in this pane displays a summary of the configured options in the Details tab.

Sorting Listed Business Objects

Objects listed in the Business Objects pane can be sorted in Ascending / Descending order based on the Business Object name and, if required, grouped by the Locked / Unlocked status of objects. Sort options are available for selection from the Business Objects sort drop-down.

Adding or Editing Business Objects

When Add-ing or Edit-ing business objects, you are prompted for the database table that forms the basis of the business object.

Note that the name you enter for the business object is limited to 63 alphanumeric characters and can include hyphens (-), underscores (_), and full-stops (.). By default, READ and SEARCH operations are available to all new business objects.

Defining Business Object Rules

The Structure pane allows you to define rules for a selected business object:

Defining a Lookup Rule

Lookups return a value from a child table intended to replace a value in the parent table, typically used in scenarios where one table holds ID-VALUE pairs and other tables reference those values by a foreign key link. For example, in the BPA Platform Event Log, the Event Category for each event record is an ID that links to the Event Category table:

Using the lookups feature, we can create an Event business object that spells out the event category in each object record rather than making the connection and lookup at task runtime.

From the Parent Details pane, choose the Column that links to the child table.

From the Lookup Details pane, configure the lookup values where:

• Table is the lookup table
• Column is the foreign key column linking back to the parent table
• Return Value is the column containing the replacement value

Lookup name is limited to 63 alphanumeric character and can include hyphens (-), underscores (_), and full-stops (.). The use of table names are prohibited.

When this node is highlighted in the Structure pane, the Details tab summarises all configuration.

Defining a Pass-Through Property Rule

Pass-through properties work in the same manner as Supplementary Reference (see About the Mapping Tab), used to store data required by the task or task designer but not passed onto SQL Server.

The value defined here is made available as an additional element in the output XML therefore the property name you define is restricted to 63 alphanumeric characters and can include hyphens (-), underscores (_), and full-stops (.). In addition, the property name must conform to XML standards, such as, no spaces or punctuation marks, and not starting with a number. There is no limit on the number of pass-through properties you create for a business object.

When this node is highlighted in the Structure pane, the Details tab summarises all configuration.

Defining a Relationship Rule

Relationships join one or more tables to the base table to form a single business object, though each relationship is defined individually.

Select the child table from the Foreign key table drop-down.

Relationship name is limited to 63 alphanumeric characters and can include hyphens (-), underscores (_), and fullstops (.).

When a relationship is created, a new Relationships tab is displayed at the bottom of the window:

Drag the relevant columns from the Parent pane to their equivalent in the Child pane. Do this for every parent child relationship.

Reloading and Refreshing Business Object tables

Reload and Refresh options enable an update to the database table properties using either the previously loaded cached version of the schema (see About the SQL Server Connection > Database Node) or direct from the underlying database. This prevents the need for a possibly time-consuming Cache Refresh. This functionality is useful if, for example, only a minor change has taken place in a single table not necessitating a refresh of the entire database schema.

• Reload from Cache — will reload the current cached version of the table but also removes any custom object definitions for that selected table.
• Reload from Database — will reload the SQL Server table including any keys, column information, new and deleted columns. This operation will also remove any custom object definitions for that selected table.
• Refresh from Cache — will refresh the current cached version table without removing any custom object definitions for that selected table.
• Refresh from Database — will refresh the SQL Server table including any keys, column information, new and deleted columns. This operation will not remove any custom object definitions for that selected table.

Adjusting Available Column Data for Task Steps

When base table and relationship nodes are highlighted in the Structure pane, a new Table tab is displayed at the bottom of the window:

All columns of the highlighted table are shown along with any rules defined in SQL Server itself. You can manipulate these columns for the business object:

• Hide — By default, all table columns are made available to the business object (and therefore are available for mapping, see About the Mapping Tab). You can choose to hide individual columns or all (select the Hide heading). Note that hiding a Required field may result in task runtime errors as a value must be mapped.
• Auto Identity — This is a read-only field indicating those fields that are auto-populated by SQL Server. Unless Required, these fields can be hidden as mapping is not required.
• Key — Keys require mapping at task design and run time. The Microsoft SQL Server Connector automatically marks primary and foreign keys as indicated by SQL Server but you can also add additional keys which must also be mapped.
• Required — Required fields must be mapped at task design time and contain values at task runtime. Such fields are indicated with ◀ in the Mapping tab. Select the Required heading to make all fields required.
• Update on Null — This rule handles how NULL values are handled at task runtime:
  • When selected, NULL or empty values are treated as an update — the existing value is cleared
  • When left unselected, fields with NULL or empty values are left unchanged

  Select or clear the Update on Null heading to make all columns have the same behaviour.

Locking Business Objects

Once created, business objects can be password locked, preventing unintentional changes or deletion. When exported, locked business objects are encrypted to prevent the structure from being viewed or altered.

Lock a Business Object:

With one or more business object selected in the Business Objects pane, click the Lock button.

The selected Business Objects will be locked and will display a locked status icon in the Business Objects pane.

Unlock a Business Object:

With the required business object or objects selected in the Business Objects pane, click the Unlock button. The Unlock Business Object dialog is displayed.

Importing and Exporting Defined Business Objects

You can export the business object configuration you have created as a backup or to use in another installation of the Microsoft SQL Server Connector. The configuration is exported as an XML file. At the time of writing, you must export all business objects found here; you cannot choose a selection of objects for export.

Similarly, you must import all business objects found in the XML file, rather than choosing a selection. If any object already exists in the this Microsoft SQL Server Connector installation, you are prompted for which action to take (Replace, Do Nothing, or Create New (note that Create New appends (_1) to the object name after import)).

Step Configuration

When creating new tasks, the Microsoft SQL Server Connector tool is located under Data Connectors of the Task Browser.

To add a new Microsoft SQL Server Connector step to an existing task, do the following:
From the relevant task, either:

• Click and drag the Microsoft SQL Server Connector icon from the Task Browser to the task Design area.
• From the task’s Design tab, right-click on empty space and select New > Data Connectors > Microsoft SQL Server Connector.

For a detailed description of how to create new tasks, refer to the product help.

About the General Tab

Use the General tab to choose the BPA Platform data source to be mapped to the SQL Server objects. You can map data source objects from either an XML or recordset source.

Provide a meaningful Name and Description for this step.

Data source can either be:

• No data source — If you do not make use of a dedicated XML or recordset input source, select this option to use BPA Platform variables in place of the XML or recordset objects’ fields.
  For example, you can extract various bits of information from an email and store them in BPA Platform variables. The variables are then mapped to fields in the selected business object and then passed to SQL Server tables (objects) at task runtime.
• Task step — The data source can be set to an available BPA Platform XML or recordset data source. Only those steps that are capable of natively exposing an XML document or recordset at runtime are listed. This may be another Microsoft SQL Server Connector step, or a tool such as Transform Data, Retrieve Text Message, or Database Query (ODBC).
• Custom schema — An XML schema defines the structure of the parsed XML: what tags are present, and the nesting of the tags. You Define the schema of the XML that is used as the input data source for this step. The Microsoft SQL Server Connector tool uses the industry standard XSD format. Any XML processed by this step must conform to this schema else an error will be reported. If using a recordset input data source, this option does not apply.
  If the XSD schema is available, either import it into the Custom Schema Configuration (use the Import XSD/XML File button), or copy and paste it into the configuration box.
  
  If the XSD schema is not available, you can import an example of the runtime XML (Import XSD/XML File), or copy and paste it into the configuration box. Use the Parse button to create the schema.
  • Input source variable — As well as defining the schema, specify the BPA Platform variable that contains the XML data at runtime. If using a recordset input data source, this option does not apply.

About the Connection Tab

You must specify the SQL Server connection this task step must use.

All connections created in the global configuration are presented here.
Alternatively, you can use a BPA Platform variable or formula to create a dynamic connection, where the connection used is determined by runtime circumstances. At runtime, the contents of the variable must match the name of one of the global connections — this is case-sensitive.

About the Recordset Tab

The Recordset tab is only available when a recordset is chosen as the Data Source in the General tab — see About the General Tab. SQL Server only accepts XML from the Microsoft SQL Server Connector, therefore you must use this tab to create the XML structure expected by the Mapping tab (see About the Mapping Tab). based on the parent and child relationships of an SQL Server table.

Additionally, you can create an XML structure to enable looping on child elements of an object — for example, adding multiple addresses and contact details to a single business partner. For such a case, you would create the structure with a parent Key element which determine when a new header is found. For more information, refer to our knowledge base article, The recommended SQL structure to generate multiple child records.

The Recordset Columns pane displays the recordset columns as a flat list; the XML Structure pane displays the XML as you build it.

Select Enable Recordset Shaping to map recordset data to an XML structure. However, if your recordset data is of a simple structure, that is the recordset columns map to a flat XML structure, skip directly to the Mapping tab.
The Microsoft SQL Server Connector creates a basic XML structure based on the recordset data source. To set an XML element as the key, right-click on the XML element (right-hand pane) and select Set as Key.

To create a new child element, right-click on the required recordset column name and select Add new child element.

Provide a Name for the child element. This has a maximum of 128 characters and uses XML-supported characters only. The new child element appears in the XML Structure pane with the chosen recordset column as a field. To add a recordset column to the new child node, right-click on the relevant column name and select Add to. Repeat until the child element has been built.

To remove a field from a child element and all associated fields, right-click on the field in the XML Structure pane and select Remove.

About the Mapping Tab

The Mapping tab of the Microsoft SQL Server Connector allows you to define the mappings between the input data source (see About the General Tab) and that required by the SQL Server instance.

From here you can:

• Automatically map where input and output parameter names match
• Create mappings from a set of transform functions to change the data between input and output
• Use nested looping to support hierarchical data structures
• Import and export mappings so that they can be reused in other steps
• Filter the data returned for SEARCH operations

The Object drop-down shows the tables made available in the Business Object Designer. The Operations dropdown shows the operations available for the selected Object.

The left-hand Input Data pane shows those data source fields available for mapping . The right-hand Output Data pane displays those fields for the selected Object and Operation combination.

Creating Mappings

Create links by dragging and dropping a Input Data field onto its corresponding Output Data. Run-time mandatory fields as defined in the Business Object Designer are shown in bold — you can save a step with unmapped mandatory fields. Only linked fields are used in the output XML.

BPA Platform formulas and variables can be included in the source data even when using a Task step or Custom schema — create a Fixed / Dynamic function (refer to the product help) for the formula or function and link to the relevant input field.

Each operation has an additional field, SupplementaryReference, which allows for traceability when transferring data from one place to another. When mapped, the data resides locally at runtime. It is added to the output, and creates a record for reference purposes only — you can choose to map any field to SupplementaryReference to assist with checking where the data originated from or at what time the data transfer occurred, for example.

SEARCH operations include two additional fields, ReturnTopRecords and ReturnTopPercent, enabling users to set either the number of records or percentage of total records (expressed as an integer value) to be returned.

Resolving anyType Elements

Input fields marked with a ▾have been declared as anyType format in the XSD. If your Output Data requires declared matching data types, you can resolve this here. Click ▾ and select the relevant data type:

Using Functions

The Mapping tab makes use of the Data Transformation Layer (DTL) feature of BPA Platform, where you can use available functions to manipulate the data. Use the Functions pane to add transformation functions:

Functions

Aggregation — Aggregation functions define operations for specific nodes.

Node Count – This function counts the number of occurrences of a node in the input recordset or XML document. The result is then passed to the mapped output node. For more information, refer to the product help.

Sum – This function calculates the total of the values from all iterations of an element in the input recordset or XML document. The sum of the values is used as the new value to an output element. For more information, refer to the product help.

Data — Data functions perform operations on input data to generate new output data.

Fixed/Dynamic – This function passes a static or dynamic value to the output XML schema. Use a variable or recordset column to generate the dynamic data. For more information, refer to the product help.

Run VBScript – Use this function to perform VBScript operations to process input data, generate output data, or both. For more information, refer to the product help.

Lookup — Lookup functions are used to find values in a nominated source, by a key.

External Lookup – Use this function to lookup values from an external database. This uses existing Database Query (ODBC) or Database Query (OLEDB) global connections. For more information, refer to the product help.

Internal Lookup – Use this function to find an alternative value for input data from a predefined lookup table. For more information, refer to the product help.

Looping — Looping functions loop through the input recordset or XML document to perform functions on all iterations of a node.

Interleaved Merge – This function loops through the input recordset or XML document and merges data from specified elements into a single occurrence for the mapped output data. For more information, refer to the product help.

Simple Loop – This function loops through the input recordset or XML document and creates an output data node for every iteration of an input node it finds. For more information, refer to the product help.

Split by type – This function is the opposite to the Interleaved Merge function in that it takes data from a single input node and splits it into two or more output data nodes. For more information, refer to the product help.

Additional Functionality

In the Transformation Mappings pane, click:

to import and use an existing mappings file (.DTLX) from a previous task

to export the current mappings for use in another task or as a backup (.DTLX)

to reset all current mappings

to delete the highlighted mapping

to delete the highlighted function

Refreshing the Tool Input Schema

Use Schema Refresh to reflect any changes made to the underlying tables and columns of your mapped business object — Ensure that necessary refreshing of the cache (see About the SQL Server Connection > Database Node) or database (see About the Business Object Designer Tab) has taken place in the relevant global connection.

Using Filters

When using SEARCH operations, you can make use of filters to limit the data involved. At runtime, these fields are treated as “where clauses”.

By default, all mapped fields are treated as “equal to” filters. To set a filter condition, right-click the required field and select Set Filter Condition. Change the filter condition as required.

Available filter conditions are:

The presence of multiple filter fields implies that all conditions must be met. Nominated fields must be mapped to be included in the output XML.

Further Mapping Controls for an Element

You can further control how mapped data is processed for each mapped element, for example, if expected data is missing. To do this, right-click the required element and select Options:

If the input source XML is missing this mapped element (mapped so therefore is required), use the options in the If mapping cannot be made due to missing data pane to control the Microsoft SQL Server Connector’s behaviour.

If the input source XML contains the mapped element but has no value, that is, received as either <element></element> or <element/>, use the options in the If mapping cannot be made due to empty data pane to control the Microsoft SQL Server Connector’s behaviour.

• Create Empty Element — Send <element></element> to SQL Server
• Create Element containing — If a missing element or data is received, always send the element with the value specified in the provided text box
• Don’t create Element — Don’t include the element in the XML sent to SQL Server
• Error — At task runtime, the Microsoft SQL Server Connector step fails and an error is written to the BPA Platform Event Log

Supported Objects

Due to the nature of Microsoft SQL Server, all objects are user-defined, and therefore differ from installation to installation. All available tables in your SQL Server instance are exposed and can be made available as objects in the Business Object Designer (see About the Business Object Designer Tab) for selection in associated task steps.

The following operations are available for all objects, however with the exception of READ and SEARCH operations which are present by default, the remainder must be selected for inclusion on a case by case basis for each Business Object.

• ADD — Add a new record to a specific table.
• DELETE — Delete or remove a specific record from a table, using a key.
• CASCADE DELETE — Deleting a parent record automatically removes all associated records from child tables.
  WARNING: Where cascading deletes are allowed, be aware that all linked records are deleted, including those in basic or lookup tables. For example, deleting a sales order containing a customer ID deletes the customer record in the linked table too. To prevent such scenarios, you must define a database constraint in the underlying database itself — refer to your SQL Server documentation.
• DELETE CHILD — Remove individual child records from a table, where a parent / child relationship exists. For example, a sales order line from a sales order record.
• MERGE — Enables child records to be updated or added depending on a key being available. This simplifies task design as it removes the need to have a check for existing child records and separate steps for ADD and UPDATE operations.
• SYNC — Similar to the MERGE operation however, enables child records to be updated, added or deleted depending on a key being available. Records for which no keys are supplied will be removed during the operation.
• READ — Retrieve objects for a specific record(s) using a key.
• SEARCH — Search for records based on provided parameters.
• UPDATE— Update a specific record, using a key, for a nominated table.

About the Options Tab

The Options tab allows you to define how errors in this step are handled at task runtime.

If an error occurs, you can decide whether the step should Continue processing, or terminate the step
immediately (Abort Step).

If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task). By allowing the task to Continue, you can use the error XML received back in a Save File step for investigation purposes, for example.

Using Memory

The Memory feature allows one of the following memory types to be added to the step:

• OLEDB Memory
• ODBC Memory
• Call Stored Procedure Memory
• Internal Memory

Memory definitions can be used to specify data to be memorised when processed by a Microsoft SQL Server Connector step at runtime. The captured data is then passed onto the relevant destination location, whether that is another defined SQL Server database table or an internal repository in BPA Platform.

With the Microsoft SQL Server Connector it is possible to memorise inputs to the tool step, as well as the following outputs relating to the response from the call to SQL Server:

Use of the Memory option in the Microsoft SQL Server Connector alters the processing method of the step at runtime. By default, the Microsoft SQL Server Connector step submits its data to SQL Server in a single XML request; when the Memory option is defined, each record is processed on a row-by-row basis.