ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Connect to an ODBC Data Source

AliS
Alteryx Alumni (Retired)
Created

How To: Connect to an ODBC Data Source

Alteryx can connect to databases using different methods. This guide will focus on setting up an ODBC connection to SQL server, however, the same general process can be followed to connect to any database via ODBC.
 

Prerequisites

  • Alteryx Designer or Server.
  • ODBC driver for the database (if it is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found here. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information).

 

Procedure
2019.2 and later

    • Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas
    • Click the drop-down  "Connect to a File or Database."

    ole1.png

    • The Single Access Point data connections dialogue will open up. Choose the relevant data connection and select ODBC.

    idea Skyscrapers

     

    Before 2019.2

    • Click the drop-down to "Connect to a File or Database." Then select "Database Connection" > "New ODBC Connection…" The "Data Link Properties" window should appear.
    • odbc1.png
    • Click the dropdown to “Connect to a File or Database.” Then select “Database Connection”->”New ODBC Connection…” The ODBC connection window should appear

    ODBC2.png

     

    All Versions

    • A new window ODBC Connections will open

    idea Skyscrapers

    • Click the “ODBC Admin” button to go to the Windows ODBC Administrator app to set-up the connection.
    idea Skyscrapers
    • In the ODBC Data Source Administrator, click the “Add…” button to set-up the new connection. You will have the choice between adding a new Data Source using a User DSN or System DSN, see this KB article  for more details on the difference.
    • You will be directed to a window to choose the driver to use for the connection.
    • Select the driver and click “Finish”

    idea Skyscrapers

    • Select a name for this connection
    • Click the dropdown to find the SQL server you wish to connect to or type in the server name
    • Click “Next”

    idea Skyscrapers

    • On the next screen, choose which type of authentication you will be using:
      • You can choose between Integrated Windows authentication, Integrated Active Directory Authentication or login ID
    • Choosing Windows Authentication and Active Directory Integration requires no additional information as the user ID you are logged in to windows as will be recognized
    • Using login ID authentication will require you to enter the user ID and password. 
    • The DBA or IT will be able with further details on the above settings
    • Once you’ve chosen your login method, click the “Next” button

    idea Skyscrapers

    • Click “Next” through the menu screens. Change any optional settings via the menus if you wish to adjust them. Generally the default configuration should provide a successful connection, however in case of doubt feel free to check with your DBA.
    • After clicking the “Finish” button, click the "Test Data Source..." button
    • If the “TESTS COMPLETED SUCCESSFULLY!” dialogue appears, you have successfully set up your connection
    • Click “OK” twice to exit the connection wizard
    • Click “OK” to exit the ODBC administrator

    idea Skyscrapers

    • In the alteryx ODBC connection window, select the Data Source Name you’ve just created. Enter a username and password if required (optional) and push “OK”

    idea Skyscrapers

     

    • The Choose Table or Specify Query window should pop up. If there are tables already in the database, choose a table and push “OK”.

    odbc10.png

    2019.2 and later
    • Upon successful connection, the ODBC connection will now appear under "recent"
    idea Skyscrapers​​​​​​​

    Before 2019.2
    • Upon successful connection, the ODBC connection will now be stored in the “Input Data” tool’s initial dropdown

    odbc11.png

    Additional Resources

    Comments
    rohit782192
    8 - Asteroid

    Hello Team,

     

    Can you provide me a details on how we connect ODBC to Oracle.

    The Example provide is of Microsoft SQL Server.

    I want to connect to ODBC with Oracle (64 Bit)

     

    Thanks in Advance.

    NApplewh
    8 - Asteroid

    I am having many issues connecting to ODBC data sources.

     

    Any advice? 

    rohit782192
    8 - Asteroid

    I am not getting ODBC 64 bit driver.

    srikantap
    5 - Atom

    Well explained.

    tothd
    7 - Meteor

     

    olimpio
    5 - Atom

    Hi, I am a new user with Alteryx and needed some advice on connecting to Hive tables is Hadoop.

     

    it would be great if I could get some information on how do I start, thanks

     

    LisaL
    Alteryx
    Alteryx

    @olimpio 
    Your go-to resources on how to connect to databases is this list of Data Sources and the help for Input Data tool.  Many of the Data Sources have articles in Community, and Hadoop is no exception.  If you're new to Alteryx this article on how to search Community might be helpful to you.

    danishimam
    6 - Meteoroid

    Hello,

     

    Im trying to connect with mysql and was able to download and install odbc driver for mysql and created odbc data source successfully.

    In alteryx also i was able to use Input Tool to connect using the above odbc data source and get the list of tables in "Visual Query Builder", but when i switched to "SQL Editor" to test the query it giving below error "No Columns Returned" although there are data in the table.

     

    did anyone got this issue ? help requested.

     

    danishimam_0-1617898419412.png

     

    Thank you,

    Danish