Setting advanced ODBC query properties

Each advanced ODBC source query has several properties, which are set using the Properties window for the advanced ODBC source.

To set advanced ODBC query properties:

  1. Open the integration.
  2. Open the Properties window for the Advanced ODBC source.
    Select a source and choose View > <name of source> Properties or
    Right-click on the source and choose <name of source> Properties or
    Add an advanced ODBC source to the integration
  3. Enter a name and description for the advanced ODBC source query.

Name   Each advanced ODBC source query must have a name. The name should describe the type of information retrieved by the query.

Description   The description should provide information about the advanced ODBC source query, such as what type of data is retrieved by the query or what the source of data is for the query. A query can be used by multiple integrations, so be sure to provide enough information so the query can easily be used by another Integration Manager user.

  1. Enter or select a data source and SQL statement to use for the advanced ODBC query.

Data Source   This is the ODBC data source or data source created in Integration Manager from which you retrieve data. Refer to the documentation for the ODBC driver for more information about setting up ODBC data sources.

SQL Statement   The SQL statement specifies what information is retrieved by the query. You can type in a SQL statement directly, or you can use the Query Builder in Integration Manager to create a SQL statement. See Using the Query Builder window.

  1. Choose the Columns tab to display the columns returned by the query and change the datatype and size, as necessary.

Column Name   Displays the names of the data items available for the query.

Datatype   Indicates what type of data is contained in the column. The data type value is automatically retrieved from the ODBC data source. In most cases, the data type is appropriate. In other cases, you may need to change the data type to better reflect how the data is used for the integration. To change the data type, click in the Datatype column and choose an item from the drop-down list. Refer to Data types for more information about selecting appropriate data types.

Size   This value indicates the data size of each column, in bytes. In most cases, the default size is appropriate. In special cases, you may need to adjust the size to better reflect the size of the data value returned by the query.

  1. Determine which columns should be included in the integration and which items in a column are unique identifiers.

Show   Select the Show check box to include the data in this column in your integration. If you are using a large source file and you do not want all the data to be integrated, clear this check box for the columns to exclude.

Is Key  Select the Is Key check box to indicate that the data items within a column are unique identifiers. For example, you might select a column called Customer ID as Is Key to indicate that the values within a column are unique.

Integration Manager uses values marked as Is Key to identify specific rows that cause errors in the integration.

  1. Choose the Scripts tab to attach scripts that are executed before and after the query is performed. Scripts are written in VBScript, a subset of the Microsoft Visual Basic programming language. For more information, refer to Using scripts.

Documentation Feedback