Home Services Partners Company

SAS Access to Salesforce, SQL Server, Oracle and RedShift using ODBC Driver

Introduction

In this tutorial we will show you how to connect from SAS to Salesforce, SQL Server, Oracle or RedShift using our DataDirect Connect for ODBC drivers. The steps that I am using here can easily be applied to any of the other data sources that we support.

Installing and Configuring DataDirect Connect for ODBC Drivers

  1. Install the Drivers:
    Install DataDirect Connect for ODBC 32 or 64bit driver on your Linux client. Ensure that the bits of the driver match the bits of your SAS application. You can see the following video to install DataDirect Connect for ODBC Drivers.
  2. Set your environment variables:

    To help you perform this task, DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. You’ll find these scripts in your installation directory.

    These scripts configure the following environment variables:

    LD_LIBRARY_PATH
    PATH
    ODBCINI
    ODBCINST

    If using the on-premise Salesforce, Oracle Service Cloud, or MongoDB drivers you will need to have a Java SE 6 or higher installed on your Linux client and on your LD_LIBRARY_PATH.

  3. Test load the driver:

    Use the DataDirect-provided ivtestlib (or ddtestlib for 64bit) utility to test load the driver.

    You'll find this utility in the bin sub-directory of your installation. You'll need to provide it with the path to the driver as follows:

    ivtestlib /opt/progress/datadirect/lib/ivora27.so

    If your installation was successful and your environment variables are properly set, you should receive a message similar to the following:

    Load of /opt/progress/datadirect/lib/ivora27.so successful, qehandle is 0x944F030
    File version: 07.01.0157 (B0108, U0075)

    If you receive an error message at this point, there's a good chance that your environment variables aren't properly set.

  4. Configure a connection:

    In order to configure a connection to your database, you will want to edit the odbc.ini file (found in your installation directory).

    Here is an example of my connection information for the DataDirect Connect for ODBC Salesforce, SQL Server, Oracle, and RedShift databases:

    Salesforce Config
    SQL Server Config
    Oracle Config
    RedShift Config

    Note: Ensure that the ODBC Data Sources section of your odbc.ini file lists out the data sources you have configured. For example:

    ODBC Data Sources
  5. Test your connection:

After entering your connection details into your odbc.ini file you can test your connection using the provided example tool. The example tool is located in the samples/example folder.

If you’re unable to successfully connect, double check your login settings with your database administrator. You can also search our Knowledge Base for the error message you received: http://knowledgebase.progress.com/

Installing SAS and Running a Query

  1. Install the required SAS products:

    Now that you have a working connection to your database, it’s time to install the necessary SAS software. In this example, I installed the SAS Foundation 9.4 along with the SAS/ACCESS interface to ODBC

  2. Launch SAS Foundation: 

    When you install SAS Foundation, you're prompted to provide an installation directory. I chose /SASHome. SAS then created a subdirectory called SASFoundation/9.4, which is where you'll find the SAS command that launches the product. If you're unable to locate the SAS installation, contact your SAS administrator for more details.

    Note: ensure that the environment variables previously set are still set when you execute the SAS application

  3. Run a basic SAS query:

    Once SAS Foundation is launched, you can access its query capabilities by switching into the SAS Log window and choosing Tools -> Query:

    SAS Tools

  4. This launches the SAS: Query dialog. Once this is running, choose the Tools -> Switch Access Mode -> ODBC menu option:

SAS Query

SAS now displays a dialog box where you can enter in the data source, username, and password details that you configured earlier.

Salesforce Login

Once you press OK, SAS will attempt to connect, via the SAS/ACCESS interface to ODBC and the DataDirect driver to your source. If everything is configured correctly, you should see a list of all available tables. From here you can select the table (or tables) from the list:

Salesforce Query

You can then select any columns you want to include in your query and then choose the Tools -> Run Query -> Run Immediate to launch your query:

Salesforce Run Immediate

This will return results either to your SAS output window or to a web browser – depending on how you have your SAS Foundation configured:

Salesforce Result

 

Next Steps

Installing SAS and running a query will remain the same for other ODBC drivers including SQL Server, Oracle and RedShift. If you would like to access a data source using DataDirect Cloud and its ODBC driver then please read the following tutorial. For more information, please visit our SAS solution page.
TUTORIAL

SAS Access to Salesforce, SQL Server, Oracle and RedShift using ODBC Driver

jdbc

DataDirect
ODBC connectors

Real-Time Access to XML Data

community

DataDirect
ODBC Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers