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.
Tip
Designer allows you to configure connection timeout for ODBC data sources. The feature improves the overall connectivity experience when connecting to remote data sources or data sources operating in peak times that are not able to respond within the 30-second timeframe. You can refer to the Community article to find out more on how the connection timeout can be set.
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, select Start > All Programs.
In Alteryx, in the Input Data tool Configuration window, select Connect a File or Database and select Other Databases > ODBC > ODBC Admin.
In Alteryx, in the Output Data tool Configuration window, select Write to File or Database > Other Databases > ODBC Admin
Select the Drivers tab and verify that the driver appears in the list of ODBC drivers installed on your computer.
Select 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. Select Add.
In the Create New Data Source window, select the ODBC Driver and select 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.
Select Mechanism and select the appropriate authentication and provide required information. See Manual connection setup for more information on authentication requirements.
Select Advanced Options to configure advanced driver options.
Select Test to test the connection, then select OK.
Select OK.
Once you have created a Data Source Name (DSN) for your connection, select the DSN from within the#input-data-tool-6845644Input 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:
In Alteryx, in the Input Data tool Configuration window, click Connect a File or Database and click Other Databases > OleDB
In Alteryx, in the Output Data tool Configuration window, click Write to File or Database > Other Databases > OleDB
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.
Warning
For an OLEDB connection to SQL Server when using username and password the Persist Security Info parameter has to be set to True.
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.