How to Integrate Salesforce Data Tables into Virtuoso via ODBC
Why (Problem Description)
Salesforce provides customer relationship management (CRM) software that allows companies and users to log and utilize customer activity. ODBC drivers do exist for Salesforce, but they only provide a starting step for integrating your Salesforce database with other data sources.
Integration is a necessity as data sources expand and correlation between each is needed; rather than keeping each separate. Without the proper tools for integration, tasks become endeavors that are unnecessarily complex, time-consuming, expensive, and damage any attempt of agile data use.
What (Solution Description)
Virtuoso can integrate data through its conductor interface or the command line
How (Solution Activation)
- A functional Salesforce ODBC DSN, using an ODBC Driver for Salesforce.com Data Sources
- Admin privileges on an instance of OpenLink Virtuoso (Commercial Edition), v7.0 or later (get your free 15 day evaluation here), running on the same host as the Salesforce ODBC DSN
Step 1. Install an ODBC Driver for Salesforce, and create an ODBC DSN for your Salesforce data
Install the ODBC Driver and configure a data source as appropriate to your environment (e.g., in your ODBC.ini file on Unix-like OS; in your Registry on Windows). Be sure to use the ODBC driver’s location on your machine, and the security token provided in your Salesforce account settings. For example:
[ODBC Data Sources]
Salesforce = Salesforce ODBC Driver
Description=Salesforce ODBC Driver DSN
## Description is optional, and is only to describe the DSN
## Driver: The location of the ODBC driver library file
## Credential values can be set here, or at connection time
Of course, you’ll also need to install a license (may be permanent or evaluation) for the driver before trying to make an ODBC connection.
Step 2. Link Salesforce Table(s) into your Virtuoso instance via ODBC
In the Virtuoso Conductor, drill down to Database → External Data Sources → Data Sources.
Click on connect to begin the external data source connection process. Authenticate using your Salesforce account credentials.
Upon successful authentication, your Virtuoso instance will be connected to the ODBC driver and ready to link Tables (and/or other DB objects such as Views and Stored Procedures).
To begin linking, click on Link objects and check the tables you want to attach to your Virtuoso instance. For this example, only the Lead table is selected. Click Link to proceed.
Select and configure data source information as desired and click link to complete the linking process.
Step 3. Test Connection
Upon successful linking, you will be able to test a query by clicking on the new table’s link and clicking Execute in the interactive SQL tool that pops up.
The data from your linked Salesforce table will be displayed in the interactive SQL window.
From here you are free to continue integrating with other SQL data from stored in Virtuoso natively, or from other external data sources.