Skip to main content

[en] ODBC and OLEDB Database Connections

[en] 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.

[en] 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.

[en] Generic ODBC Option

[en] 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.

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

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

[en] Create an ODBC Database Connection

[en] 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.

[en] To configure an ODBC connection:

  1. [en] Open ODBC Administrator for the driver you installed using one of these methods:

    • [en] On your computer, select Start > All Programs.

    • [en] In Alteryx, in the Input Data tool Configuration window, select Connect a File or Database and select Other Databases > ODBC > ODBC Admin.

    • [en] In Alteryx, in the Output Data tool Configuration window, select Write to File or Database > Other Databases > ODBC Admin

  2. [en] Select the Drivers tab and verify that the driver appears in the list of ODBC drivers installed on your computer.

  3. [en] 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.

  4. [en] In the Create New Data Source window, select the ODBC Driver and select Finish.

  5. [en] In the ODBC Driver DSN Setup window, in Data Source Name type a name for the DSN.

  6. [en] In Description type details about the DSN.

  7. [en] In Host type the IP address or host name of the server.

  8. [en] In Port, type the service listening port number.

  9. [en] Select Mechanism and select the appropriate authentication and provide required information. See Manual connection setup for more information on authentication requirements.

  10. [en] Select Advanced Options to configure advanced driver options.

  11. [en] Select Test to test the connection, then select OK.

  12. [en] Select OK.

[en] 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.

[en] Create an OLEDB database connection

[en] 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.

[en] To configure an OLEDB connection:

  1. [en] Open the Data Link Properties window using one of these methods:

    • [en] In Alteryx, in the Input Data tool Configuration window, click Connect a File or Database and click Other Databases > OleDB

    • [en] In Alteryx, in the Output Data tool Configuration window, click Write to File or Database > Other Databases > OleDB

  2. [en] Open the Data Link Properties window, select a provider on the Provider tab.

  3. [en] Click the Connection tab.

  4. [en] Select or type a server name.

  5. [en] 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.

    警告

    [en] For an OLEDB connection to SQL Server when using username and password the Persist Security Info parameter has to be set to True.

  6. [en] Select the Allow saving password check box. See Password Encryption for more on password encryption considerations.

  7. [en] Click Test Connection.

  8. [en] Click OK or select the Advanced or All tabs to view and set other initialization properties required by the driver if necessary.

: