ODBC Connector > Mappings and mapping tasks with ODBC Connector > ODBC sources in mappings

ODBC sources in mappings

In a mapping, you can configure a Source transformation to represent a single ODBC source, multiple ODBC sources, ODBC query or ODBC parameter.
The following table describes the ODBC source properties that you can configure in a source transformation:
Name of the source connection.
You can select an existing connection, create a new connection, or define parameter values for the source connection property.
If you want to overwrite the source connection properties at runtime, select the Allow parameter to be overridden at run time option.
Specify the parameter file directory and name in the advanced session properties.
Source Type
Type of source object. Select Single Object, Multiple Objects, Query or Parameter.
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object.
The Parameter property appears only if you select parameter as the source type.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Name of the source object. Select the source object for the task.
Objects and Relationships
Adds multiple objects. Click on Add Source Object.
Note: The Objects and Relationships property appears only if you select Multiple Objects as the source type.
Click on Define Query and enter a valid custom query.
Note: The Query property appears only if you select Query as the source type.
The parameter for the source object. Create or select the parameter for the source object.
Note: The parameter property appears only if you select Parameter as the source type.
Filters records and reduces the number of rows that the Secure Agent reads from the source. Add conditions in a read operation to filter records from the source.
Sorts records based on the conditions you specify.
Select distinct rows only
Eliminates duplicate rows. Select to eliminate duplicate rows.
Default is false.
Tracing Level
Sets the amount of detail that appears in the log file. Select Normal, Verbose Initialization or Verbose Data. Default is normal.
Executes SQL query before loading records to the database. For example, if you want to delete the records from database before the latest records load, write a Pre SQL.
Post SQL
Executes SQL query after loading records to the database.
Output is Deterministic
Specify only when the source output does not change between session runs.
Output is Repeatable
Specify only when the order of the source output is same between the session runs. Select Never or Always.
SQL Override
The SQL statement to override the default query generated from the specified source type to read data from the ODBC source.

Key range partitioning

You can configure key range partitioning when you use a mapping task to read data from ODBC sources. With key range partitioning, the Secure Agent distributes rows of source data based on the field that you define as partition keys. The Secure Agent compares the field value to the range values for each partition and sends rows to the appropriate partitions.
Use key range partitioning for columns that have an even distribution of data values. Otherwise, the partitions might have unequal size. For example, a column might have 10 rows between key values 1 and 1000 and the column might have 999 rows between key values 1001 and 2000. If the mapping includes multiple sources, use the same number of key ranges for each source.
When you define key range partitioning for a column, the Secure Agent reads the rows that are within the specified partition range. For example, if you configure two partitions for a column with the ranges as 10 through 20 and 30 through 40, the Secure Agent does not read the rows 20 through 30 because these rows are not within the specified partition range.
Consider the following rules when you configure partitioning:

Configure key range partitioning

Perform the following steps to configure key range partitioning for ODBC sources:
    1. In the Source Properties, click the Partitions tab.
    2. Select the required partition key from the list.
    3. Click Add New Key Range to define the number of partitions and the key ranges based on which the Secure Agent must partition data.
    Use a blank value for the start range to indicate the minimum value. Use a blank value for the end range to indicate the maximum value.
    The following image displays the Partitions tab: