Create a connection in Anaplan Data Orchestrator to import data from Microsoft SQL Server (MSSQL). Then use the connection to extract data and create a source dataset.

Prerequisites

Before you create a connection, you need:

  • An MSSQL instance hosted on a cloud platform, such as Amazon Web Services (AWS), Azure, Google Cloud Platform (GCP), or any other cloud provider. The solution is compatible with on-premises SQL Server deployments.
  • An unencrypted Certificate Authority (CA) certificate in Privacy-Enhanced Mail (PEM) format.
  • A database user with a password and permissions (at least read permission). 
    For example:

USE {database name};
CREATE LOGIN {user name}
 WITH PASSWORD = '{password}';
CREATE USER {user name} FOR LOGIN {user name};
EXEC sp_addrolemember 'db_datareader', '{user name}';
EXEC sp_addrolemember '{role name}', '{user name}'; 


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. Select Connections on the left-side panel.
  3. Select Create connection.
  4. Select Microsoft SQL Server (MSSQL) and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  5. On the Connection details page, 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.
  6. On the Connection credentials page, enter your MSSQL credentials, and then select Next.
    For information about the fields on this page, see SSH tunneling options for MSSQL connections.
  7. After the connection test is complete, select Done.

Extract data from the MSSQL connection

You can extract data from the MSSQL connection to add source data to Data Orchestrator. The data extract creates a source dataset.

To extract data:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Select Source data from the left-side panel.
  3. Select Add data > From connection.
  4. On the Dataset details page, enter these details and select Next:
    • Connection: Select the MSSQL connection you want to base the dataset on. 
    • Dataset name: Create a name for the dataset.
    • Description: Enter a description about the dataset.
    • Namespace: Select a namespace. This is the MSSQL schema for the specific database defined for the connection. 
    • Path name: Select a path name. This is the MSSQL table name. 
  5. On the Choose an upload type page, enter these details and select Next:
    1. Select the Load type:
      • Full replace: Completely replaces the current loaded data with the new data.
      • Append: Adds the new data to the end of the current table.
      • Incremental: Takes the new data and incrementally updates the contents of the dataset based on matching the primary key fields. Exiting data is updated and new data is appended.
    2. Select the columns to import.
  6. Select Create in the confirmation dialog.