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.

The following chart shows the inputs of the 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 one of the following: