Skip to main content

Data provisioning

A generic database connector can be used to enable the data provisioning to SQL (Microsoft SQL Server and Oracle-based systems are supported).

Review the following practical example to learn how to use this connectivity.

Configuring data provisioning using the generic database connector (practical example)​

Goal: Update the email of an identity in a database table named Identity. The unique key used to locate the record is UID.

Prerequisites: Ensure that the connector is configured correctly. See Generic SQL - SQL generic database.

  1. Define the data model.
<connectorDataModel xmlns="http://schemas.omada.net/ops/2015/ConnectorDataModelML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <properties>
    <property name="UID" dataType="stringType"/>
    <property name="Email" dataType="stringType"/>
  </properties>
  <objects>
    <object name="Identity">
      <objectDetails>
        <objectDetail name="Schema" value="dbo" />
      </objectDetails>
      <objectProperties>
        <objectProperty isKey="true">UID</objectProperty>
        <objectProperty>Email</objectProperty>
      </objectProperties>
    </object>
  </objects>
</connectorDataModel>
  • UID is marked as the key (isKey="true") that is used in the WHERE clause during updates and deletes.
  • Email is the attribute we want to update in the database.
  • The object name (Identity) must match the name of the table in the target database.
  • <objectDetail name="Schema" value="dbo" /> defines the schema used in the database query, and must match the actual schema where the table resides.
  1. Configure the task mapping to map the identity attributes to the database fields:
  • UID is mapped to DOLM_IDENTITYID.
  • Email is mapped to DOLM_EMAIL.
  • Standard fields such as Object Id, Object type, and Operation are required for the task to run correctly.

Expected result:

Example identity: Anna Becker

  • UID: ABAA
  • Current email: ABAA@megamart.com

After changing the identity's email to annabecker@megamart.com, the provisioning is triggered and the connector executes the following statement:

UPDATE dbo.Identity
SET Email = 'ANNABECKER@MEGAMART.COM'
WHERE UID = 'ABAA';

The change is reflected directly in the database: