Using the Flexible SQL Connector
Data gateway enables Archer to connect to and interact with external datasets. Connectors enable you to create connections between external datasets and Archer applications and questionnaires. The Flexible SQL Connector comes with Archer. You can use the Flexible SQL Connector to build as many connections to your external database as required. As an alternative, you can build your own connector. For more information, see Creating a Data Gateway Connector.
On this page
Before you begin
- Ensure that your external database is a Microsoft SQL Server.
- Identify all tables and views from your external database that you wish to use in Archer.
Note: Each table or view connected to Archer must have a separate connection.
- Ensure that your external database has a single column primary key.
- Verify that all fields of interest are supported. The Flexible SQL Connector supports the following field types: Date, Text, Numeric, and IP.
- Ensure that the account has data gateway access. For more information, see Assigning Rights to Access Roles.
- Ensure that your network allows access from Archer to your external database. See Microsoft SQL documentation for details.
Review the relationship between Archer data and external system
- Identify the column in the external data that identifies the content in the Archer application. This column must contain a unique value for each row.
- Ensure that your Archer application has enough fields to contain the external data. Use these Archer fields to map external data.
Understanding the inputs of the Flexible SQL Connector
There are several inputs to the Flexible SQL Connector that must be accounted for when connecting external data.
Note: The Archer Interface guides you through the process of configuring a Flexible SQL connector.
Properties |
Data Type |
Description |
---|---|---|
dbProviderType |
Character String Limit of 256 characters |
Indicates the type of external resource. Use the following for this value: System.Data.SqlClient |
connectionString |
Character String Limit of 256 characters |
Represents the string that is required for connecting to the external resource. Use a standard SQL connection string. For example: "Data Source=SERVERNAME;Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD;TransparentNetworkIPResolution=False" Replace SERVERNAME, DATABASE, USERNAME, and PASSWORD with the appropriate values for your environment. Note: There is no default maximum or minimum number of connections. You can set this by adding a Min Pool Size or Max Pool Size parameter to the connectionString. Note: Integrated Authentication is available. Use Integrated Security=true; instead of User ID and Password in your connectionString. For more details regarding the connectionString, see Microsoft SQL documentation. |
tableName |
Character String Limit of 256 characters |
Indicates the table or view inside the external resource. |
idColumnName |
Character String Limit of 256 characters |
Corresponds to the column name. Uniquely identifies a record in the external system. |
idColumnType |
Character String Limit of 256 characters |
Indicates the record identifier datatype. Supported datatypes are Integer and String. |
Use the Flexible SQL Connector to create a connection to external data with Archer
To configure your data gateway connections, do 1 of the following:
- Use Archer. For more information, see Configure Data Gateway Connections.
- Use the Configure Data Gateway Connections (RESTful API) commands.