ODBC/ODBC32 agent

The ODBC/ODBC32 agent is used only for on-premises deployments.

An ODBC/ODBC32 agent can be used as a source and can serve as a destination for:

  • Excel
  • MySQL
  • ODBC/ODBC32
  • PowerBI
  • SQL Server
  • Text

Requirements

If you want to use the ODBC agent to connect to your source data, on the Controller server:

  • Install the ODBC driver (64-bit) provided by your source system’s vendor.

  • Create a System DSN in ODBC Data Source (64-bit), configured using the source system’s vendor specifications.

Add a connection

To add a connection for an ODBC or ODBC32 agent:

  1. In the navigation panel, select Setup.
  2. Select an ODBC or ODBC32 agent or peer connection, and click > Add Connection.
  3. Connection Name must be unique. Up to 40 characters.
  4. System DSN: Can use spaces and be up 32 characters, but none of the following: [ ] { } , ; ? * = ! @ \.
  5. UserID: (Optional) Up to 256 characters; no restrictions.
  6. Password: (Optional) Up to 256 characters; no restrictions.
  7. Click Save.

Add a query

  1. Select a SQL connection or peer query, and click > Add Query.
  2. Query Name must be unique. Up to 40 characters.

  3. In Query, type or paste your ERP query.

     Note:  The SQL must be compatible with your ERP.

    Example of a query with parameters:

     Note:  Within the body of your SQL script, you must preprend @ to all parameters. For example, startDate must appear as @startDate.

    The same query viewed in the Data Integration process:

  4. If the query has parameters, add them to the Filters table:

    • Name: The parameter name. Up to 50 characters.

       Tip:  In the filter table, don't prepend "@" to the parameter names.

    • Type: The parameter's data type (text or numeric). (For date values choose text.)
    • Default value: What the user sees in the Data Integration process and can edit. Up to 50 characters.
    • Sample value: What the user sees when hovering over the cell in the Data Integration process. Use it to show the expected input/format. Up to 50 characters.
    • Hide: Turn on if you want to keep users of the Data Integration process from seeing the parameter, while using its Default value in the query.
  5. Click Save.