SAS Access to Marketo and Eloqua using DataDirect Cloud ODBC Driver

In this tutorial we will show you how to connect from SAS to Marketo or Eloqua using our DataDirect Cloud ODBC drivers. The steps that I am using here can easily be applied to any of the other data sources that DataDirect Cloud supports.

1. Install the Drivers:
   Install ODBC 32 or 64bit driver for DataDirect Cloud on your Linux client. Ensure that the bit of the driver matches that of your SAS application. You can refer to the following DataDirect Cloud ODBC Driver Installation guide.
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
   TZ

   If using the on-premises Salesforce or Oracle Service Cloud 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 to a Data Source that I have created in DataDirect Cloud:

   
   

   For more information on creating Data Sources in DataDirect Cloud you can watch these videos on configuring Marketo and Eloqua.

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

   
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/

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:

   

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

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

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:

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:

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

 

Installing SAS and running a query will remain the same for other DataDirect Cloud data sources including Eloqua, Hubspot and Google Analytics. If you would like to access a data source using Connect for ODBC driver then please read the following tutorial. For more information, please visit our SAS solution page.