Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Tell a Database connection type at a glance

HenrietteH
Alteryx
Alteryx
Created

How To: Tell a Database connection type at a glance

 

Below is a quick overview of how the different types of database connections appear within "workflow dependencies." This will help determine and compare different connection types from a quick glance.

 

Prerequisites

 

  • Product - Alteryx
  • Database connection(s)

 

Procedure

 

To view connections in the Workflow Dependencies, go to Options > Advanced Options > Workflow Dependencies. (The same notes apply for connections viewed through the Input/Output tool configurations except for the Gallery Connection).

 

2019-09-24_16-52-50.png

 

1. Saved Data Connection

Saved Data Connections can be recognized by the aka: at the beginning of the Connection String. They include all connections the user created through Options > Advanced Options > Manage Data Connections as well as Oracle and SQL Server connections created through the "Quick Connect" option. Also see https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Database-Connections-Creating-an-al...

Saved Data Connections are saved in xml files on the user's machine with passwords encrypted.

%AppData%\Roaming\Alteryx\Engine\UserAlias.xml contains user connections and %ProgramData%\Alteryx\Engine\SystemAlias.xml contains system connections.

 

2. In-DB Connection

In-DB Connections show the connection name the user picked when setting up the connection. All In-DB Connections are saved with encrypted passwords either in %AppData%\Roaming\Alteryx\Engine\UserConnections.xml for user connections or %ProgramData%\Alteryx\Engine\SystemConnections.xml for system connections. 

 

3. In-DB Connection using a file

In-DB connections using files point to the file storing the connection information. You can recognize these by the file extension .indbc. They also include the path to the .indbc file.

 

4. Gallery Connection

Gallery connections are created by the Gallery admin and then shared with the user so they can use them on their local machine. They start with aka: like regular saved data connections. When viewed in the Input tool, you can see the connection name. In the workflow dependencies they show up as the key used to identify them in MongoDB. 

 

5. DSN-less ODBC Connection

Any connection using an ODBC driver starts with the keyword odbc: For a DSN-less connection, all relevant connection information is stored in the connection string. 

 

6. ODBC w/DSN

An ODBC connection using a DSN on the user's machine starts with the keyword odbc: then points to the DSN name. Additional parameters may be added to the connection string, such as username and password, they will override the information for the connection parameters stored with the DSN. 

 

7. OleDB Connection

An OleDB connection starts with the keyword odb: and contains all relevant connection information in the connection string. When selecting OleDB as the connection option in Alteryx, a wizard helps build the connection string. 

 

8. Oracle OCI

An Oracle OCI connection starts with the keyword oci: and requires a tnsnames.ora file. By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory.

 

9. 32-bit Connection

32-bit connections can be made as long as a 32-bit driver is available (For ODBC, OleDB, Oracle clients). They can be recognized by the 32bit: keyword BEFORE any other keywords indicating the type of connection to use. 

 

10. (SQL Server) Bulk connection

Bulk connections have their own prefixes to let Alteryx know to expect a bulk connection, e.g. ssvb indicates a SQL Server Bulk Loader connection. They may have additional parameters added to the connection string.  

 

 

Additional Resources

 

Comments
KateP
Alteryx
Alteryx

Awesome!!!