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 tool to 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.
Create an OLEDB database connection
To connect via OLEDB, first install the OLEDB driver on your machine. After installing the driver you can then select it from a list of drivers available in the Alteryx tool you are using to connect to the database.
To configure an OLEDB connection:
- Open the Data Link Properties window using one of these methods:
- Open the Data Link Properties window, select a provider on the Provider tab.
- Click the Connection tab.
- Select or type a server name.
- Select the type of authentication and, if necessary, type a user name in User name and a password in Password. See Manual connection setup for more information on authentication requirements.
- Select the Allow saving password check box. See Password Encryption for more on password encryption considerations.
- Click Test Connection.
- Click OK or select the Advanced or All tabs to view and set other initialization properties required by the driver if necessary.