Import from Azure SQL / Pipeline Cloud

Civis Platform supports data ingestion from EveryAction’s Cloud Pipeline, built on Azure, via database sync. The following help documentation outlines the specific steps required for setup with Azure’s custom credential type. 

Step 1: Create Platform Remote Host 

First, add the Azure SQL instance as a remote host in Platform.  To do this, navigate to the remote hosts page by clicking your initials in the top right corner, “Remote Hosts”, “New Remote Host”, “Database Connection through JDBC”,  (or click here to be redirected)  and fill out with the following information:

• The name should be set to something that you and your organization will recognize and remember. 
• The url must be a long string with the following format: 

jdbc:sqlserver://[yourhost.com];databaseName=[databaseName];serverName=[serverName];clientId=[clientId];tenantId=[tenantId]

The items within the [] must be filled in to match your Azure SQL instance.  This information would be provided by the Azure instance’s database administrator. Please contact them directly for additional assistance on how to find the correct values.

Step 2: Create Platform Credential  

Next, create a Platform credential with your Azure database credential.

To do this, navigate to the credentials page by clicking “Admin” in the top navigation bar, “Credentials”  (or click here to be redirected), select the Create Credentials button, and enter the following:

• The name should be set to something that you and your organization will recognize and remember, such as “[org name] Cloud Pipeline”. 
• The credential ‘type’ should be set to ‘database’. 
• The username field must be set to the Azure database’s public key.  This will begin with  -----BEGIN CERTIFICATE----- and end with -----END CERTIFICATE-----, and the entire key, including the BEGIN and END tags, must be included.
• The password must be set to the private key for the database.  This will begin with -----BEGIN PRIVATE KEY----- and end with -----END PRIVATE KEY-----,and the entire key, including the BEGIN and END tags, must be included.
• In the remote host dropdown, select the remote host you created in Step 1. 

Username and password should be filled in with the keys provided by the Azure instance’s database administrator. Please contact them directly for additional assistance on how to find the correct keys.

Step 3: Create Database Import in Platform 

Now that you have stored your Azure connection information (the remote host and credential from Step 1 and Step 2) in Platform, set up a database import job to sync data from Azure SQL to Redshift (Platform). 

To set up the import job, navigate to the new Import page by clicking “Data” in the top navigation bar, then under “Imports”, click “Database” (or click here to be redirected), and filling in the following information: 

• Source will be the Azure SQL database, named the same as the remote and credential in steps 1 and 2.
• Schema (on left) will be the location of the data you want to import within the Azure database.  The name of the schema will be managed by your organization or the organization from which you are getting the data. 

• Destination will be your Civis Platform Redshift database.
• Schema (on right) will be the location for where you want the imported data to be put within the Civis database. 

For additional detail on database imports, follow this documentation. Details on advanced settings can be found here. 

As with all Civis imports, this can be a one-time job, or can be scheduled to run as needed.