10-07-2019 09:56 AM - edited 12-07-2021 07:10 AM
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.
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).
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 seehttps://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.
Awesome!!!
For #9 - 32-bit Connection, Alteryx version 2021.2 is the last version that contains functionality to connect to 32-bit data sources. See End of Support for 32-bit Connections for further details.