Skip to content

Knowledge Base

White Paper Libary

Microsoft SQL Server Connector v1.2

Introduction to the Microsoft SQL Server Connector

What is the Microsoft SQL Server Connector?

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.

When combined with other tools of BPA Platform, the Microsoft SQL Server Connector can also act as a key component when synchronising data between two systems that do not naturally talk to each other; for example, as part of an integration for an ERP or CRM system and an eCommerce web shop.

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.
  • Business Object Designer controls which databases and tables are available at task design and runtime.
  • 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.
  • Ability to create Freehand Query Objects using custom script to execute specific actions against a connection’s SQL Server database.
  • 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.
  • Option to suppress XML data output for NULL or empty column values.
  • Ability to save partial connections to allow users to either complete configuration at a separate time or to export a connection without its current authentication credentials.

The Microsoft SQL Server Connector Tool Pack

The tool pack consists of:

  • Microsoft SQL Server Connector Agent — The Agent communicates directly with SQL Server. Depending on the required configuration, 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 (mandatory for cloud to on-premise connectivity using our Remote Data Relay).
  • 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.2

White Paper - Microsoft SQL Server Connector v1.2

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 2023 or above.

Minimum SQL Server Version

The following versions of SQL Server are supported:

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

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 in a number of commonly used deployments. Other variations are supported. For further information, contact your Codeless Platforms account manager.

Example On-Premise Architecture

This diagram shows a overview of BPA Platform and the Microsoft SQL Server Connector in an on-premise deployment. This includes configurations where the BPA Platform server is located in the same network or domain as the SQL Server instances or databases, either on-premise itself or on a cloud-based virtual machine.

This example assumes that each component exists within the same network or domain.

Microsoft SQL Server Connector v1.2

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.

Microsoft SQL Server Connector v1.2

Example Hybrid Architecture

A hybrid deployment can be created where connections are made to SQL Server databases are either located onpremise or in the cloud. As with the Example Cloud Architecture diagram, all members of the Azure SQL family are supported.

In this deployment type, all on-premise components are assumed to reside within the same network or domain. Note that BPA Platform can additionally be installed on a cloud-based virtual machine within that same domain.

Microsoft SQL Server Connector v1.2

Example Partner-Hosted Cloud BPA Platform with On-Premise SQL Server Architecture

BPA Platform 2023 and higher supports connectivity from a cloud partner-hosted installation of BPA Platform to individual on-premise SQL Server databases where there is no direct network connectivity available.

The following diagram describes the architecture between a single BPA Platform server instance and one SQL Server database, however this can be scaled to include multiple BPA Platform server instances connecting to multiple SQL Server databases for different customers.

Microsoft SQL Server Connector v1.2

Where BPA Platform is hosted in the cloud by a partner for example, and SQL Server exists on-premise in a customer’s external network, connectivity can be established using our Remote Data Relay. This enables secure communication between the customer’s BPA Platform server instance and their on-premise SQL Server database.

The Remote Data Relay can be most effectively used in environments where multiple cloud-hosted BPA Platform instances reside on a single Windows Server and connectivity is required to one or more customer databases located in one or more external networks.

The Microsoft SQL Server Connector tool must be installed on the BPA Platform server, and also on any remote BPA Platform client computers that run Microsoft SQL Server Connector tasks.

Working with Other Tools

The Microsoft SQL Server Connector can directly interact with the following BPA Platform tools:

Consuming XML from Other Tools

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

IconTool NameTool Category
Call Stored Procedure (OLEDB) ToolCall Stored Procedure (OLEDB)Input, Data Connectors, Output, and Execute
Database Query ODBC ToolDatabase Query (ODBC)Input and Data Connectors
Database Query OLEDB ToolDatabase Query (OLEDB)Input and Data Connectors
Import Flat File ToolImport Flat FileInput
Microsoft SQL Server Connector v1.2Import XML DocumentInput
Microsoft SQL Server Connector v1.2Retrieve Text MessageInput
Microsoft SQL Server Connector v1.2Convert Recordset to XMLFormat
Convert XML to Recordset ToolConvert XML to RecordsetFormat
Microsoft SQL Server Connector v1.2Transform DataFormat
Microsoft SQL Server Connector v1.2Call TaskExecute
Microsoft SQL Server Connector v1.2Applications Platform ConnectorData Connectors
Microsoft SQL Server Connector v1.2Microsoft SQL Server ConnectorData 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:

IconTool NameTool Category
Microsoft SQL Server Connector v1.2Retrieve Text MessageInput
Convert XML to Recordset ToolConvert XML to RecordsetFormat
Run Microsoft Reporting Services ToolRun Microsoft Reporting ServicesFormat
Microsoft SQL Server Connector v1.2Save FileOutput
Microsoft SQL Server Connector v1.2Call TaskExecute
Microsoft SQL Server Connector v1.2Applications Platform ConnectorData Connectors
Microsoft SQL Server Connector v1.2Web Service ConnectorData Connectors
Microsoft SQL Server Connector v1.2Microsoft SQL Server ConnectorData 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:

Microsoft SQL Server Connector v1.2

  • 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.

Microsoft SQL Server Connector v1.2

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.

The Microsoft SQL Server Connector supports saving partially configured connections. This enables users to return to a previously incomplete connection to finalise details, or for previously configured connections to have certain details removed, for example, to log out from the current authenticated user account. Such connections are shown in the Global Configuration as Configuration Incomplete.

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.

Microsoft SQL Server Connector v1.2

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.

About the Agent Connection > General Node

Use the General node to determine which Microsoft SQL Server Connector Agent this global connection will use. The General node requires you to specify how communication will be established between the Microsoft SQL Server Connector and a SQL Server database instance, whether directly through the Integration Agent (the standard configuration) or through our Remote Data Relay (if the BPA Platform server is hosted in the cloud and the SQL Server database instance you are connecting to is located within an external network).

Microsoft SQL Server Connector v1.2

The Agent Connection panel enables the selection of the connection method:

  • Use Integration Agent
    This is the standard communication method where connectivity between the Microsoft SQL Server Connector and a SQL Server database instance is made directly using our Integration Agent within the same network.

    • Specify the Connection 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, connection is made over HTTP on TCP port 4210. If the Agent has been set up to use HTTPS or a different port, enable Show Advanced Options. See About the Agent Connection > Advanced Node.
  • Use Remote Data Relay
    This option enables secure connectivity between a cloud-based server hosting BPA Platform and an on-premise SQL Server database where no direct network connection is readily available.
    • Selection of Relay Settings opens the Remote Data Relay Setup dialog.
    • Previously configured Remote Data Relay configurations can be removed using Remove Relay Settings.

Configuration of the connection to use the Remote Data Relay is described within the product help.

Use Test Connection to ensure the Microsoft SQL Server Connector can communicate to either the Integration Agent or the Remote Data Relay successfully.

About the Agent Connection > Advanced Node

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

Microsoft SQL Server Connector v1.2

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.

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

Microsoft SQL Server Connector v1.2

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 SQL Username and SQL 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 account log-in credentials used for the connection to the Microsoft SQL Server Connector Agent service 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 with 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.

Microsoft SQL Server Connector v1.2

Choose the relevant database from the SQL Server 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.

A Business Object can comprise of either:

  • a configuration of one or more tables in your SQL Server database, with customisation available to restrict which operations will be available in the tool step and which relationships may exist between them.
  • a Freehand Query Object where bespoke SQL script is used to create a single executable query operation against one or tables in your SQL Server database.

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

Microsoft SQL Server Connector v1.2

The Business Objects pane lists those objects made available for this connection. This includes standard Business Objects and Freehand Query Objects. 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 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.

Business Objects and Freehand Query Objects

The Business Object Designer enables the entry of both Business Objects and Freehand Query Objects. These objects are then made available during task step design.

Adding Business Objects

When Add-ing a new Business Object, you are initially prompted as the type of object to be created. Select either Business Object for a standard object or Freehand Query Object to create an object using custom script.

Microsoft SQL Server Connector v1.2

Adding or Editing Standard Business Objects

Selection of the Business Object option displays the New Business Object dialog:

Microsoft SQL Server Connector v1.2

Use the drop-down to select the required base table for the business object. Table selection is made from the SQL Database assigned to the connection in the global configuration (see About the SQL Server Connection > Database Node).

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 Standard Business Object Rules

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

Microsoft SQL Server Connector v1.2

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:

Microsoft SQL Server Connector v1.2

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.

Microsoft SQL Server Connector v1.2

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.

Microsoft SQL Server Connector v1.2

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.

Microsoft SQL Server Connector v1.2

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:

Microsoft SQL Server Connector v1.2

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 Standard 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:

Microsoft SQL Server Connector v1.2

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 displayed in bold on the Operation Data pane and indicated by a triangle icon in the Transformation Mappings pane. Select the Required heading to make all fields required.
  • Update on Null — This rule handles how incoming 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.

Adding or Editing Freehand Query Objects

Freehand Query Objects can be Add-ed or Edit-ed to support bespoke SQL query results which may not easily be achieved using a standard Business Object. For example a SQL query may require more complex statements to facilitate a specific result. The Freehand Query Object designer allows custom or one-off queries to be either manually entered or copied from another source such as SQL Server Management Studio. Such objects will appear in the Connector tool step assigned as an Execute operation.

Microsoft SQL Server Connector v1.2

Enter a name for the Freehand Query Object. This will be used to identify the object on the Mapping tab when creating a task step – see About the Mapping Tab.

Note that the name you enter for the Freehand Query Object is limited to 63 alphanumeric characters and can include hyphens (-), underscores (_), and full-stops (.).

In the Freehand SQL Query pane, enter the SQL script for the freehand query.

The Insert Parameter button will place a parameter marker into the SQL script at the current cursor position; alternatively you can manually enter parameters using the following syntax {parameterName}.

Click Execute Query or hit the F5 key to validate the entered script and run the query. If parameters are present in the query, sample or example values should be provided here to allow the query to successfully run.

Microsoft SQL Server Connector v1.2

The Persist Values option enables sample or example parameter values to be retained within the Freehand Query Object. These values are only used when the query is executed during the creation or amendment of the object and do not affect the query in a task step at runtime. If this setting is unchecked, then any entered values are not saved for future use after the query is executed.

Click Reset Values to remove all previously entered values, if required.

Once executed, the Output Preview pane will display the resulting top 20 records returned from the SQL Server database. The Input Schema pane will present the parameter fields which will be present in the tool step’s Mapping tab for this Freehand Query Object. The Output Schema pane will show the schema structure which will be returned by the tool at runtime.

Microsoft SQL Server Connector v1.2

Duplicating Business Objects

An existing Business Object can be duplicated to create an exact copy. This may be useful, for example, if distinct changes are required which may subtly alter the available options within an object or specific operation.

With the Business Objects to be duplicated selected, click the Duplicate button. The duplicate object or objects will retain the name of the original items with the addition of a numerical suffix (i.e. _1). Once created, these duplicated objects can be edited either to rename them or amend their configuration.

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. Both standard Business Objects and Freehand Query Objects can be locked.

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

Microsoft SQL Server Connector v1.2

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

Unlocking Business Objects:

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

Microsoft SQL Server Connector v1.2

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 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)).

Microsoft SQL Server Connector v1.2

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.
    -OR-
  • 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.

Microsoft SQL Server Connector v1.2

On the General tab, 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.
    Microsoft SQL Server Connector v1.2

    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.

Microsoft SQL Server Connector v1.2

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 present if a recordset source is used as the input to the tool step. Selection of the Enable Recordset Shaping option enables the creation of a nested XML schema structure from a flat recordset input for use in the tool’s Mapping tab and subsequently at runtime.

Microsoft SQL Server Connector v1.2

By default, the XML Structure pane will display the flattened structure from the input recordset (as displayed in the Recordset Columns pane). This can then be manipulated as required.

Adding a child element to the XML schema

One or more items in the Recordset Columns pane can be selected and added as child elements in the XML schema. Multiple levels of child objects are permitted.

Microsoft SQL Server Connector v1.2

With the required items selected in the Recordset Columns pane, select Add New Child Element option using the right-click menu.

Enter the new Child Element name, including the path if you are adding the new entry to an existing child element.

In the example below, the new element Cases will become a child element of the Contacts element.

Microsoft SQL Server Connector v1.2
Microsoft SQL Server Connector v1.2

Additional items can be added to child elements by using drag-and-drop to move them from the Recordset Columns pane to the XML Structure pane, or by right-clicking the selected Recordset Columns items and choosing Add from the resulting menu options.

Hiding recordset elements in the XML schema

Items can be set to a hidden status in the XML Structure and will no longer appear in the tool step for mapping. To do this, right click one or more items in the Recordset Columns pane and select the Hide in Header option from the pop-up menu. Hidden items no longer appear in XML Structure and are displayed greyed out in the Recordset Columns pane.

To reinstate the hidden recordset items in the XML schema, right-click on the hidden item in the Recordset Columns pane and select the Show in Header option from the pop-up menu.

Set Key elements in the XML schema

Individual items in the XML Structure can be flagged as Key fields requiring mapping by selecting the Set As Key option in the right-click menu. These are then denoted by the (Key) text shown appended to the field name in the XML Structure pane.

Removing items from the XML schema

Items in the XML structure can be for removed from use in the tool’s Mapping tab and subsequently at runtime by right-clicking on the required items in the XML Structure pane and selecting the Remove option from the pop-up menu.

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.

Microsoft SQL Server Connector v1.2

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
  • Set filter conditions against specific fields in SEARCH operations

The Object drop-down shows the standard Business Objects and Freehand Query Objects made available in the Business Object Designer. The Operations drop-down 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 Operation 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 Operation 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 variable and link to the
relevant Operation Data 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.

This field is present in all operations in addition to any defined Pass-Through Property fields which may have been created in the selected standard Business Object or Freehand Query Object.

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 Operation Data requires declared matching data types, you can resolve this here. Click and select the relevant data type:

Microsoft SQL Server Connector v1.2

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. For further details on any of the functions, refer to the product help.

Functions

Aggregation — Aggregation functions define operations for specific nodes.

Microsoft SQL Server Connector v1.2 Node Count -This function counts the number of occurrences of a node in the source recordset or
XML document. The result is then passed to the mapped Operation Data node.

Microsoft SQL Server Connector v1.2 Sum – This function calculates the total of the values from all iterations of an element in the source recordset or XML document. The sum of the values is used as the new value to an Operation Data element.

Data — Data functions perform operations on source data to generate new data to be passed to the mapped Operation Data element.

Microsoft SQL Server Connector v1.2 Fixed/Dynamic – This function passes a static or dynamic value to the Operation Data XML schema. Use a variable or recordset column to generate the dynamic data.

Microsoft SQL Server Connector v1.2 Run VBScript – Use this function to perform VBScript operations to process source data, generate data to be submitted in the call at runtime, or both.

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

Microsoft SQL Server Connector v1.2 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.

Microsoft SQL Server Connector v1.2 Internal Lookup – Use this function to find an alternative value for source data from a predefined lookup
table.

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

Microsoft SQL Server Connector v1.2 Interleaved Merge – This function loops through the source recordset or XML document and merges data from specified elements into a single occurrence for the mapped Operation Data element. For more information, refer to the product help.

Microsoft SQL Server Connector v1.2 Simple Loop – This function loops through the source recordset or XML document and creates an Operation Data node for every iteration of an source node it finds.

Microsoft SQL Server Connector v1.2 Split by type – This function is the opposite to the Interleaved Merge function in that it takes data from
a single source node and splits it into two or more Operation Data nodes.

Additional Functionality

In the Transformation Mappings pane, click:

Microsoft SQL Server Connector v1.2 to import and use an existing mappings file (.DTLX) from a previous task

Microsoft SQL Server Connector v1.2 to export the current mappings for use in another task or as a backup (.DTLX)

Microsoft SQL Server Connector v1.2 to reset all current mappings

Microsoft SQL Server Connector v1.2 to delete the highlighted mapping

Microsoft SQL Server Connector v1.2 to delete the highlighted function

Refreshing the Tool Input Schema

Use Schema Refresh to reflect any changes made to the structure of your mapped Business Object operation — 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.

Microsoft SQL Server Connector v1.2

Available filter conditions are:

FilterDescription
=Filter results where the Operation Data value must be equal to the mapped Input Data value.
< >Filter results where the data value returned at runtime value is not equal to the mapped
data source value.
<Filter results where the Operation Data value is less than the mapped Input Data value.
>Filter results where the Operation Data value is greater than the mapped Input Data value.
<=Filter results where the data value returned at runtime value is less than or equal to the
mapped data source value.
>=Filter results where the data value returned at runtime value is more than or equal to the
mapped data source value.
IS NULLFilter results where the Operation Data value is NULL.
Note that a dummy value must be supplied for this filter to be processed correctly — it is ignored at task runtime.
IS NOT NULLFilter results where the Operation Data value is not NULL.
Note that a dummy value must be supplied for this filter to be processed correctly — it is
ignored at task runtime.
LIKEFilter results where the data source value contains the specified string and any wildcards.
Standard regular expression wildcards are supported. If the database values contain
regular expression characters as actual values, for example, the percentage character (%)
or question mark (?), wrap the whole search string in angle brackets. For example, <5%>.
NOT LIKEFilter results where the data source value excludes the specified string and any wildcards.
Standard regular expression wildcards are supported. If the database values contain
regular expression characters as actual values, for example, the percentage character (%)
or question mark (?), wrap the whole search string in angle brackets. For example, <5%>.

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

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:

Microsoft SQL Server Connector v1.2

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.
  • 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.

Freehand Query Objects

  • EXECUTE— Runs the custom query script defined in the Freehand Query Object against the defined table or tables.

About the Options Tab

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

Microsoft SQL Server Connector v1.2

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.

Microsoft SQL Server Connector v1.2

Use of the Memory option in the Microsoft SQL Server Connector alters the processing method of the steps 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.

Videos

Quickstart Guide: Microsoft SQL Server Connector | Standard Installation and Configuration


Quickstart Guide: Microsoft SQL Server Connector | Installation, Configuration and Remote Data Relay Setup


Quickstart Guide: Microsoft SQL Server Connector | Business Object Designer


Quickstart Guide: Microsoft SQL Server Connector | Freehand Query Object and Task Example

White Paper - Microsoft SQL Server Connector v1.2

White Paper - Microsoft SQL Server Connector v1.2

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.