You can import data from Microsoft SQL Server (MSSQL) to create source datasets in Anaplan Data Orchestrator (ADO). To import data, you need a connection to MSSQL and either a pipeline or a connection extract.

You must first create a connection to MSSQL with the MSSQL connector in Data Orchestrator. Then use that connection to either create a pipeline or use a connection extract for data import.

Connectivity prerequisites

Before you can create a connection to MSSQL, you need to meet these prerequisites.

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:

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

Use the MSSQL connector in Data Orchestrator to 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 the Microsoft SQL Server (MSSQL) connector and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  6. Enter these details on the Connection details screen, and then 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. Enter your MSSQL credentials on the Connection credentials screen, 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.

Import data

You can import data from MSSQL with a pipeline or a connection extract. Use the connection you created to configure the pipeline or connection extract.