How to set up Salesforce ODBC connection

[TimothyR]

How to setup ODBC connection with Salesforce

Driver installation

Obtain and install driver:

• If non-admin – Reach out to your license admin to obtain/install the Simba Salesforce Driver

• If an admin

  1.  Go to licenses.alteryx.com and log in via your Alteryx SSO account

  2.  In the left menu, navigate to Drivers and download the Salesforce driver installer

Set up ODBC connection DSN connection
 

1. Add Input tool to canvas

2. Click “Connect a File or Database” dropdown of tool and select Data sources

3. On the bottom right menu, click “ODBC” under “Generic connection”

4. On the ODBC connection pop-up, access the ODBC Admin at the bottom left of the pane. This will open up a new window outside of Designer

 

4. Go to the User DSN tab and click Add on the right

   • if multiple users will use the machine and SF connection or you are configuring the connection within an Alteryx Server environment, go to System DSN instead.

5. Select the "Simba Salesforce ODBC Driver" you installed previously and click Finish.

6. Provide a data source name of your choosing and enter in your security token (leave username and password blank as this will be entered in Designer) and check the "Save Security Token" box in order to utilize the token with Designer.

.

4. Click Ok and restart Designer. Navigate back to the Generic ODBC connection menu and select the new DSN you just created and enter your username and password to connect

.

DSN-less connection

You can also utilize a DSN-less connection string if preferred directly in the Input tool. Below is an example template (replacing anything with curly brackets with your respective information):

odbc:Driver=Simba Salesforce ODBC Driver; UID={email}; PWD={password}; SecurityToken={security token}; ApiVersion=47.0

You’ll also notice that the DSN-less connection will allow you to specific a particular API version.

Unsupported features

Salesforce reports within the Visual Query Builder. Stored procedures would be the equivalent of Reports within Salesforce and stored procedures are not supported within the Visual Query Builder for the Salesforce Driver

OAuth. While OAuth is supported with the driver itself, it is currently not supported within the Alteryx software

Additional Resources

• List one title per line and embed the link for each
• Example Alteryx Designer Help

[tstrutt]

Thanks for the information - however there is just about zero information out there (that I am able to locate anyways) around using the Output Tool with ODBC connections of any kind to update data.  In particular with Salesforce.  Although this driver exists and Alteryx advises to use it to connect, do they have any information on output to Salesforce using the standard tool and these Simba Drivers?

Here is an additional post on the community site asking for information around the Output Tool using Simba Salesforce ODBC driver connection as well.

Any additional how-to would be appreciated.

-Thomas

[BGirkins]

I have to say these drivers are much less user friendly than the historical connectors. Not impressed and adds more work/time in any module referencing SFDC data. I am not sure what is worse.

1. Updating the known "defect" (not a bug even though it says it is a bug) with existing SFDC connectors fighting over Python versions

2. Replacing all of those connectors and configurations with this driver and re-building the configurations

Would be nice to see a fix to #1.

[lepome]

@tstrutt 
Regarding connecting to data sources in general, please look at the Help section about Data Sources.  There is also this KB article on Connecting to an ODBC Data Source.  Further, if you are likely to publish a workflow that connects to a Data Source, whether reading with the Input Data tool or writing with the Output Data tool, this KB article on Managing Data Connections Between Server/Scheduler and Local Machine is indispensable. 

@BGirkins 
I hear you.  I'm not certain from your description, but I think the issues you refer to are security-related, so that might be the reason that the "fix" you seek has not yet been implemented.
I assume you're referring to the latest version of the SalesForce Input tool. Generally, when requesting functionality, it is best done as follows:  Look for the idea in the Ideas section of Community. If it has been suggested within the past couple of years, Like the post, and add your use case in the comments including the specific changes you want to see implemented. Our product team pays particular attention to ideas submitted by users.

[BGirkins]

@lepome Yeah I am referring to this:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sales-Force-Input-Tool-You-Found-a-Bug...

As well as:

Error: Salesforce Input (208): Traceback (most recent call last):  File "main.py", line 14, in <module>ModuleNotFoundError: No module named 'engine.sf_proxy'

I have a support call later. The Input tool simply has a Python conflict which is "known". So hopefully both errors will be addressed soon. I had mentioned to my support rep the Gallery page for the Input connector should be updated. It says, "Compatible with Alteryx version 2019.3 and later," which is not true. It is not compatible with some of the latest versions of Alteryx.

[BGirkins]

Follow up after the support call. Using the AMP engine removes the bug references in the article above. So updating System Settings to 'Use Both Engines' and then updating User Settings so the default engine is AMP is the work around. Also confirmed that while the ODBC driver will be supported and has many benefits, the Python-based connectors are still going to be the focus and Alteryx will be supporting them. That said, the error above will not be addressed since AMP engine is the "Alteryx-suggested" engine moving forward.

[tstrutt]

@LisaL

Appreciate the info; however installing ODBC driver and setting up/selecting the DSN isn't the trouble I have with the Simba Salesforce ODBC tool.  Rather, understanding exactly how that tool updates/inserts records is more the gap.  Additionally there are a number of issues the ODBC Salesforce driver has against certain standard fields in salesforce.  And the Salesforce Output tool isn't great to use as it wipes credentials any time the flow is opened from the gallery.  This appears to occur when it pulls the workflow out of the database into staging.

From my (and a large number of other large Salesforce shops') view there doesn't seem to be great connectivity and support with Salesforce using Alteryx.  I have seen other ETL tools start to increase their connectivity/functionality with Salesforce, however Alteryx seems to be slagging behind on this a ways.

@BGirkins

Yeah the spread of how the SF Input vs Output tools function is frustrating.  Additionally for the tools that are not a macro/workflow, but rather python scripted, it is a lot more difficult to find out what version of the Salesforce API is being used.  This becomes super important as we dive into Salesforce annually deprecating old API versions.  A couple notes below on some things that helped fix some additional connector tool issues I had.  We are currently testing the ODBC connector across out lines of business to ensure we can get an acceptable/stable output connection to salesforce so our processes don't hit a stop.

Registry Editor Updates:

  There are two RegistryEditor Changes I made under Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\SRC\Alteryx

     -Add New >> String Value >>  'PythonAllowHideEncryption' - set to 'true' -- This was recommended somewhere in the alteryx community to fix the credential issue.  I didn't experience any benefits.

     -Delete >> UserEncryption >> set to 'true' -- Myself and another user (but no other designer users) had this in our Registry Editor.  We found this was preventing us from using any gallery connections and also was causing credentials to wipe on ANY Salesforce tool.  After removing this we were able to see the SF Input tool credentials no longer wipe as well as were able to use gallery connections.

[lepome]

@tstrutt @BGirkins  Thank you for the clarification.  I can only lend a sympathetic ear.  I don't have solutions for you regarding precisely how the ODBC driver does what it does nor how to address the Python credentials encryption issues. Encrypting credentials is a complicated issue and we generally don't want to recommend that users mess around in the registry.  If you do have a need to tweak registry keys, be certain to back them up beforehand.  Better yet, open a Support case through the Case Portal on the Support page if you have a paid license.

For users who might read this in the future, be certain to pay attention if upgrading from a Designer or Server version with a number less than or equal to 2020.3  to a version greater than or equal to 2020.4.  See this info:  Alteryx Embedded Python.

[Mildman]

If you need access to the Salesforce sandbox environment, you DSN less connection string will be;

ODBC: Driver={Simba Salesforce ODBC Driver};UID={username};PWD={password};SecurityToken={token};URL={https://test.salesforce.com};ApiVersion=47.0