You can create a pipeline that writes back data from an Anaplan Data Orchestrator dataset to Microsoft SQL Server (MSSQL).

You must create a connection to MSSQL before you can create a pipeline.

Connectivity prerequisites

Before you can create a connection to MSSQL, you need the connectivity prerequisites listed in this table.

ItemNotes
MSSQL account

You must have active access to an MSSQL account and instance where the target tables reside. 

For example, the MSSQL instance can be hosted on these cloud platforms: Amazon Web Services (AWS), Azure, Google Cloud Platform (GCP), or any other cloud provider. The solution is compatible with on-premises SQL Server deployments.

Certificate Authority (CA) certificateAn unencrypted CA certificate in a Privacy-Enhanced Mail (PEM) format.
Database user

A database user with a password and permissions (at least read permission).

For example:

CREATE USER <user_name> IDENTIFIED BY 'your_password_here';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *&ZeroWidthSpace;.* TO <user_name>;

Appropriate permissions

The MSSQL user associated with the connection must have the necessary privileges to write data to the target table.

This typically requires CONNECT privilege on the database, and INSERT privilege on the target table (or the target schema). 

Target table in MSSQLA target table must already exist within your MSSQL database.   

Create a connection to MSSQL

The MSSQL connector also supports connections to Azure SQL.

You will need your MSSQL or Azure credentials. View the MSSQL or Azure documentation for more information about your credentials.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections on the left-side panel.
  4. Select Create connection.
  5. Select Microsoft SQL Server (MSSQL) and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  6. On the Connection details screen, enter these details and select Next:
    • Name: Create a name for your connection. The name can contain alphanumeric characters and underscores.
    • Description: Enter a description about your connection.
  7. On the Connection credentials screen, enter your MSSQL credentials, and then select Next.
    For information about the fields on the Connection credentials screen, see SSH tunneling options for MSSQL connections.
  8. After the connection test is complete, select Done.

Create a writeback pipeline

When you set up the writeback pipeline, you'll use your connection to export either a source dataset or a transformation view dataset (derived dataset) from Data Orchestrator to MSSQL.

To create a writeback pipeline:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Pipelines from the left-side panel.
  4. Select Create pipeline.
  5. Enter a Name for your pipeline and then select Create.
    You are taken to the pipeline designer view.
  6. Select the Source icon, and then complete these steps in the right-side panel:
    1. Select Anaplan from the Connection type dropdown. 
    2. Select Datasets from the Choose connection dropdown.
    3. Select Source location, choose a Data Orchestrator dataset, and then select Confirm.
      The dataset is used as the source for your pipeline.
    4. Enter a new Label if you want to change the source display name in the designer view.
      By default, the name of the source dataset you selected displays as the label name.
  7. Optionally, select the add icon that appears between the Source and Sink nodes.
    You can add steps to your pipeline to process data.
  8. Select the Sink icon, and then complete these steps in the right-side panel:
    1. Select MSSQL from the Connection type dropdown.
    2. Select the MSSQL connection you created from the Choose connection dropdown.
    3. Select Target location, select an MSSQL table, and then select Done.
    4. Select Target mapping, map the Data Orchestrator source dataset values to the MSSQL target values, and then select Done.
    5. Select the desired Write option in the right-side panel.
      This determines how data is written to the target table. Your write options are described in this table:
Write optionsDescription
Append

Adds all rows from the source data to the existing rows in the target table.

If you select Append as the write option, determine if you want to configure a staging table. To configure the staging table, see the Staging table section below.

To prevent data mismatch errors with MSSQL, make sure your data in Data Orchestrator and in MSSQL have a schema alignment. This means the tables in Data Orchestrator and in MSSQL must have the same columns and data types.

Note: Append load is not suitable for target tables with primary keys. 

Full replaceDeletes all existing data in the target table and replaces it with the source data.
Upsert

Updates existing rows and adds new rows based on a specified key. 

If you select Upsert as the write option, select Conflict columns, which are used to identify existing rows when you use this write option.  

You also need to determine if you want to configure a staging table. See the Staging table section below for more information.

To prevent data mismatch errors with MSSQL, make sure your data in Data Orchestrator and in MSSQL have a schema alignment. This means the tables in Data Orchestrator and in MSSQL must have the same columns and data types.

  1. Enter a new Label if you want to change the sink display name in the designer view.
    By default, the name of the target table you selected displays as the label name.
  2. Select Publish, and then select Run to execute the data transfer.

Staging table preparations

Staging tables are used to compare and process data, before Data Orchestrator loads the data to the final target table in MSSQL.

When you configure the Sink details, Data Orchestrator uses a staging table in MSSQL if you select the Append or Upsert write option. The Specify staging table name checkbox displays after you select the Append or Upsert write option.

By default, the checkbox is unselected, and Data Orchestrator creates the staging table in MSSQL. If Data Orchestrator doesn't have access to create tables in MSSQL, you must manually create and provide the staging table information in Data Orchestrator.

There are two ways to create staging tables:

  • Let Data Orchestrator automatically create a staging table in MSSQL.
  • Manually create a staging table in MSSQL.

Verify the writeback in MSSQL 

Once the pipeline is successfully completed, you can log in to your MSSQL account to verify the data load. 

Navigate to the target database, schema, and table specified in the pipeline configuration. The data written from the Data Orchestrator dataset is available in the target table, based on the selected write option (append, full replace, or upsert).