ODBC and OLEDB Database Connections

Designer can access data that resides in a database and bring the data into memory in Designer for processing, or process the data within the database where it resides.

Use the Input Data Tool and Output Data Tool to connect to a database and read in, or write out, data for in-memory processing. Use the Connect In-DB Tool or Data Stream In Tool to connect to the database, read the data, and build the query that is sent to the database when the workflow is run.

Generic ODBC Option

From the Connect In-DB tool, you can select the Generic ODBC option to attempt a connection to an unsupported data source. This option does not guarantee a successful connection to unsupported data sources; however, data sources that are similar to Microsoft SQL Server have the best chance of success.

Designer supports ODBC and OLEDB database connections, various data platforms and drivers. See Supported Data Sources and File Formats.

This page provides steps for adding a new ODBC and OLEDB driver on your computer.

Create an ODBC Database Connection

To connect via ODBC, first install the ODBC driver on your computer. Then, use the ODBC Data Source Administrator to create a Data Source Name (DSN) for your connection. You can then select the DSN in the Alteryx tool you are using to connect to the database.

To configure an ODBC connection:

Open ODBC Administrator for the driver you installed using one of these methods:

On your computer, click Start > All Programs
In Alteryx, in the Input Data Tool Configuration window, click Connect a File or Database and click Other Databases > ODBC > ODBC Admin
In Alteryx, in the Output Data Tool Configuration window, click Write to File or Database > Other Databases > ODBC Admin

Click the Drivers tab and verify that the driver appears in the list of ODBC drivers installed on your computer.
Click the User DSN tab to create a user DSN or the System DSN tab to create a system DSN. A user DSN is specific to a user on the computer and can only be seen by the user who creates it. A system DSN can be seen by all users that log in to the computer.
Click Add.
In the Create New Data Source window, select the ODBC Driver and click Finish.
In the ODBC Driver DSN Setup window, in Data Source Name type a name for the DSN.
In Description type details about the DSN.
In Host type the IP address or host name of the server.
In Port, type the service listening port number.
Click Mechanism and select the appropriate authentication and provide required information. See Manual connection setup for more information on authentication requirements.
Click Advanced Options to configure advanced driver options.
Click Test to test the connection, then click OK.
Click OK.

Once you have created a Data Source Name (DSN) for your connection, select the DSN from within the Input Data Toolto read in data, or from within the Output Data Tool to write out data, for in-memory processing. The DSN can also be used in the Connect In-DB Tool or Data Stream In Tool to read and process data in-database via an ODBC driver.