Query Salesforce from SQL Server using PolyBase

PolyBase is a new feature of SQL Server that allows you to connect to relational and non-relational data. It allows you to run queries on any external data sources you might have like Oracle, PostgreSQL, Salesforce, MongoDB, Hadoop, etc. Combined with Progress DataDirect ODBC connectors, any query you want to run can be pushed down using PolyBase to provide you with the best performance available.

To help you get started, we put together this tutorial on how you can use PolyBase with Progress DataDirect’s ODBC Connector for Salesforce to query your external data in Salesforce. If you want to connect to any other data source like Oracle, Hadoop, MongoDB, etc., the steps are similar, so please feel free to use the respective connectors when you use this tutorial.

1. Download Progress DataDirect’s ODBC Connector for Salesforce(64-bit).
2. Extract the zip and run the installer to begin the installation.
3. After you have completed installation, open ODBC Administrator (64-bit).
4. Go to System DSN tab and Click on Add to create new DSN. Choose DataDirect 8.X Salesforce as your driver as shown below.
   
   
   
   
5. On the next screen, configure your connection as below –
   1. Data Source Name : Any Name
   2. Host Name: login.salesforce.com\
   3. User Name: Your Salesforce User Name
   4. Security Token: Optional Security Token.
      
      
      
6. Click on Test Connect – provide your Salesforce Username and Password. If you see your Test Connection is Successful, click on OK to save the configuration.

1. We assume you have PolyBase feature installed on your SQL Server instance.

1. Before you create an external data source, create a Master Key if you haven’t already.
   
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your-strong-password';
   
   
2. Next, securely create a scoped credential to store your Salesforce login credentials by running the below script.
   
   
   CREATE DATABASE SCOPED CREDENTIAL Salesforce_Cred WITH IDENTITY = 'your-salesforce-username', Secret = 'your-salesforce-password';
   
   
   
3. It’s time to create the external data source by running the below script. Don’t forget to replace DSN name and CREDENTIAL name, if you have used something else in the previous steps.
   
   
   CREATE EXTERNAL DATA SOURCE DataDirect_Salesforce

   WITH ( LOCATION = 'odbc://localhost',

   CONNECTION_OPTIONS = 'DSN=Salesforce',

   PUSHDOWN = ON,

   CREDENTIAL = Salesforce_Cred);
   
   
   
4. Now you can create an external table which you can use to query your data in Salesforce. To create the table, run the below DDL script.
   
   
   CREATE EXTERNAL TABLE OPPORTUNITYCONTACT (

   ID NVARCHAR(18) NOT NULL,

   OPPORTUNITYID NVARCHAR(18) NOT NULL,

   CONTACTID NVARCHAR(18) NOT NULL,

   "ROLE" NVARCHAR(40),

   ISPRIMARY bit NOT NULL)

   WITH (LOCATION='OPPORTUNITYCONTACTROLE', DATA_SOURCE=DataDirect_Salesforce)
    
5. The Location in the above script refers to the table name exposed from your data source via the ODBC Connector, in this case the Progress DataDirect ODBC Connector for Salesforce. The DATA_SOURCE is the external data source we just created in step 3.
   
   
6. Now, you can query your external data in Salesforce from SQL Server using below query.
   
   
   SELECT TOP 100 * FROM OPPORTUNITYCONTACT

   SELECT * FROM OPPORTUNITYCONTACT where OPPORTUNITYID = 'some-id'
   
   

1. When you are testing the connection in the ODBC Administrator, look out for this error message:

    

   Specified driver could not be loaded due to system error 126: The specified module cannot be found. (DataDirect 8.0 Salesforce, C:\Program Files\Progress\DataDirect\ODBC\drivers\ddsfrc28.dll).

    

   If you see this error, the issue might be due to the fact that Java is not installed or jvm.dll is not in the PATH. To resolve this, install Java and edit the environment variable PATH to include jvm.dll
   
   

2. When you try to create an external data source, if you see the below error -
   
   
   Msg 46530, Level 16, State 11, Line 18

   External data sources are not supported with type GENERIC.
   
   
   1. Run the below script to enable PolyBase -
      
      
      exec sp_configure @configname = 'PolyBase enabled', @configvalue = 1;

      RECONFIGURE WITH OVERRIDE;
      
      
   2. If this doesn’t resolve the issue, check the status of the services SQL Server PolyBase Data Movement and SQL Server PolyBase Engine. If they are in the status Change Pending, then you will have to enable TCP/IP in SQL Server Network Configuration as shown below.
      
      
      
      
   3. Restart SQL Server Services for this to take effect.