Create a connection in Anaplan Data Orchestrator to import data from a Secure File Transfer Protocol (SFTP) server. Then use the connection to extract data and create a source dataset.

Before you create a connection, you need:

  • An SFTP server hosted on Amazon Web Services (AWS), on any cloud service provider, or an on-premise SFTP server.
  • A user with a Secure Shell (SSH) private key with at least read permissions for the bucket and objects.
  • A folder path to a subset of the bucket connected to the SFTP server.

Create a connection to SFTP

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Select Connections from the left-side panel.
  3. Select Create connection.
  4. On the Create connection page, select SFTP, 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: Optionally, enter a description about your connection.
  6. On the Connection credentials page, enter your SFTP credentials and select Next:
    • Host: Enter your SFTP server hostname (for example, s-52d4b77da9example.server.transfer.us-east-1.amazonaws.com).
    • Port: Enter SFTP port (for example, 22).
    • Username: Enter the SFTP server user name (for example, sftp-user).
    • Private Key: Enter the SSH private key (OpenSSH format) linked to SFTP server. You can use the ssh-keygen -t rsa -b 4096 -f <keyfilename> command to generate a key pair. This will create a public key(.pub) and a private key. The public key is linked with the SFTP server and the private key is used to make connection.
      Here is an example of an SSH private key:

-----BEGIN OPENSSH PRIVATE KEY-----
b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAABlwAAAAdzc2gtcn
... (Your private key content in Base64) ...
... multiple lines ...
-----END OPENSSH PRIVATE KEY-----

  1. After the connection test is complete, select Done.

Extract data from the SFTP connection

You can extract data from the SFTP 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 SFTP connection you created.
    • Dataset Name: Enter a name for the dataset.
    • Description: Optionally, provide a description for the dataset.
    • Path Name: Specify the file path pattern (default pattern: /**/*.csv).
    • Column Separator: Choose a separator from the options: Tab, Comma, Semicolon, or Other. If you select Other, specify the custom separator.
    • Text Delimiter: Specify the character used to enclose text in your CSV (for example, ").
    • Header Row: Specify the row number where the header is located (for example, 1).
    • First Data Row: Specify the row number where the actual data starts (for example, 2).
  5. On the Choose an upload type page, enter these details and select Next:
    1. Select the Load type:
      • Full replace: Replaces the entire dataset with new data.
      • Append: Adds records from new or updated files to the existing dataset without deleting or updating previous records.
      • Incremental: Captures records from new or updated files and updates existing records in the dataset based on a matching primary key and appends records with unmatched primary keys.
    2. Select the columns to import.
      • If you choose Incremental as the load type, Primary Key (PK) is required. The system uses the PK to identify and update existing records. The Cursor Field is automatically selected and is the last updated date of the files that match the pattern. This ensures that only new or updated files are included in the sync.
      • If you choose Append as the load type, the system automatically chooses the Cursor Field. So no need for you to set it up. This enables seamless appending of new data and maintains synchronization.
  6. Select Create in the confirmation dialog.