Exporting data into Microsoft SQL Server or Snowflake

Updated by Jaspreet Bakshi

Microsoft SQL Server or Snowflake are often used by enterprises to bring together data from various sources for reporting purposes or for driving other apps and use cases.

DotAlign Cloud comes with a utility to export your DotAlign data set into SQL Server or Snowflake. To use it, please follow these steps:

  1. Download, unblock and unzip the file into a folder on your machine
  2. When you unzip the download, you will see 3 files
    1. da_profile_sync.exe – This syncs contacts and companies and associated information.
    2. da_interactions_sync.exe – This syncs email messages and meetings with participants.
    3. sample_env_file.txt - This file is a sample of how your environment file should look like. It has all the properties that are needed for the sync utilities to work.
  3. Rename the sample_env_file.txt to .env in the same folder. Please note the . at the start of the file name. This file contains the sensitive information that the utility needs, for example the credentials to use to hit the DotAlign Cloud API and the credentials to use for the SQL Server. The following specific properties need to be specified in the .env file
SERVER="full_database_server_name"
DATABASE="database_name"
PORT=1433
LOGIN="login"
PASSWORD="password"
DOTALIGN_CLOUD_API_BASE_URL="your_dotalign_url/api"
TENANT_ID="azure_tenant_id"
CLIENT_ID="client_id"
CLIENT_SECRET="client_secret"
SCOPE="authentication_scope"
GRANT_TYPE="client_credentials"
E_MAILER_SERVICE="Outlook365"
E_SMTP_HOST="smtp.office365.com"
E_SMTP_PORT="587"
E_SMTP_USER_NAME="[email protected]"
E_SMTP_PASSWORD=""
E_SMTP_LOGGING="normal"
E_SMTP_EMAIL_FROM=""
E_SMTP_EMAIL_TO="[email protected],[email protected]"
E_SMTP_EMAIL_CC="[email protected],[email protected]"
E_SMTP_SECURE="false"
E_SMTP_TLS_CIPHER="SSLv3"
SNOWFLAKE_ACCOUNT="x82733623.east-us-2.azure"
SNOWFLAKE_USER=""
SNOWFLAKE_LOGIN_AS_ROLE="" // defaults to "SYSADMIN"
SNOWFLAKE_PASSWORD=""
SNOWFLAKE_WAREHOUSE=""
SNOWFLAKE_DATABASE="DB1"
SNOWFLAKE_APPLICATION="dotalign_cloud_sync"
  1. Now you can run the sync utilities. You can run the command with a --help option to get help with the parameters.
Please note that the parameters are case sensitive

However, the only parameter you are required to specify is the --teamNumber parameter. This will tell the exporter which team's data to fetch from DotAlign Cloud. You can find the team number to use by looking at your DotAlign Cloud deployment and finding the team number there. The image below illustrates where you can find the team number on the DotAlign Cloud app.

Following is a sample run of the export utility.

As you can see it connects to the DotAlign Cloud API and fetches a batch of data, transforms it, and then connects to the SQL Server or Snowflake as configured, and writes the transformed data into the appropriate tables.

Questions? Please feel free to reach out to [email protected].

How Did We Do?