Configure field mappings in a synchronization task to map source columns to target columns.
Configure field mapping on the Field Mapping page of the synchronization task wizard.
You must map at least one source column to a target column. Map columns with compatible data types or create field expressions to convert data types appropriately.
Depending on the task operation, the synchronization task requires certain fields to be included in the field mapping. By default, the synchronization task maps the required fields. If you configure the field mapping, ensure that the required fields remain mapped. If you do not map the required fields, the synchronization task fails.
The following table shows the required fields for each applicable task operation for a database target:
Required field
Task operations
Description
Primary Keys
Delete
Update
Upsert
Map primary key columns to enable the synchronization task to identify records to delete, update, or upsert in a database target.
Non-null fields
Insert
Update
Upsert
Map all fields that cannot be null in database.
When you configure field mappings, you can also perform the following tasks:
•Edit field data types.
•Add a mapplet to the field mapping.
•Create lookups.
Field data types
When you create a synchronization task, Data Integration assigns a data type to each field in the source and target. You can edit the field datatypes on the Field Mapping page of the synchronization task wizard. You can edit field data types for any source or target type except for Data Integration Connector sources and targets, and mapplets.
Mapplets in field mappings
You can add a mapplet to a field mapping. After you add a mapplet to a field mapping, you must map the source fields to the input fields of the mapplet and map the output fields of the mapplet to the target fields.
When a source field is mapped directly to a target field and you map an output field of a mapplet to the same target field, Data Integration concatenates the values of the source and output fields in the target field. Verify that the expression is correct for the target field.
Note: The names of the output fields of a mapplet do not match the source field names. Data Integration appends a number to the end of the source field name to determine the output field name. In addition, Data Integration may not display the output fields in the same order as the source fields.
Lookup conditions
A lookup returns values based on a lookup condition. You can create a lookup condition based on information in the source on the Field Mapping page of the synchronization task wizard. For example, for a SALES source database table, you might set the ITEM_ID column equal to ITEM_ID column in a ITEMS flat file, and have the lookup return the item name for each matching item ID.
When you create a lookup condition, you define the following components:
•Lookup connection and object. The connection and object to use to perform the lookup. When possible, use a native connection. For example, to perform a lookup on an Oracle table, use an Oracle connection instead of an ODBC connection.
•Source and lookup fields. The fields used to define the lookup condition. The synchronization task compares the value of the source field against the lookup field and then returns a value based on the match. You can define multiple conditions in a lookup. If you define more than one lookup condition, all lookup conditions must be true to find the match.
For example, you define the following conditions for a lookup:
When you configure a lookup, you configure a lookup return value. The lookup return value depends on the return value properties that you define, such as multiplicity or a lookup expression.
A lookup return value is the value that Data Integration returns when it finds a match based on the lookup condition. If the lookup returns an error, Data Integration writes the row to the error rows file.
You can configure a lookup expression as part of the lookup return value. Configure a simple expression that uses the $OutputField variable to represent the lookup return value.
For example, the following expression adds 100 to each lookup return value:
$OutputField+100
As another example, you can use the concatenate operator ( || ) to append a string to a string lookup return value as follows:
'Mighty' || '$OutputField'
You can use parameters defined in a parameter file in a lookup expression.
The following table describes the lookup return value properties that you can configure:
Lookup Return Value Property
Description
Output Field
The field from the lookup table that you want to use.
Multiplicity
How Data Integration handles multiple return values.
- Error If More Than 1 Output Value. Select if the synchronization task should display an error when the lookup condition returns multiple values. Data Integration rejects rows when multiple matches are found, writing them to the error rows file. This is the default.
- Randomly Pick 1 Output Value. Select if the synchronization task should choose the first returned value when a lookup condition returns multiple values. Different systems might use different orders to return lookup values.
Expression
A simple expression that uses $OutputField to represent the selected output field.
By default, Data Integration passes the lookup return value without alteration with the following expression: $OutputField.
Rules and guidelines for lookups
Use the following rules and guidelines when creating a lookup:
•If the lookup is on a flat file, the file must use a comma delimiter. You cannot use any other type of delimiter.
•When you configure a lookup, you can configure a simple lookup expression as part of the lookup return value. Use the $OutputField variable to represent the expression. If you use a lookup expression that does not include $OutputField, you negate the action of the lookup.
•Tasks with a flat file lookup that run by a Secure Agent on Windows 7 (64 bit) might not complete. To resolve the issue, configure a network login for the Secure Agent service.
•On the Field Mapping page, you can perform a lookup or create an expression for each source field. You cannot do both.
•Each task can contain one or more lookups. To avoid impacting performance, include less than six lookups in a task.
•When performing the lookup, the task performs an outerjoin and does not sort the input rows. The lookup performs a string comparison that is not case-sensitive to determine matching rows.
• The source field and lookup field in the lookup condition must have compatible data types. If the data types are not compatible, the following error appears:
Source field [<source field name> (<source field data type>)] and lookup field [<lookup field name> (<lookup field data type>)] have incompatible data types.
If you create multiple lookup conditions on a lookup field and the lookup source is a flat file, all source fields must have the same data type. The synchronization task uses the larger precision and scale of the source field data types as the precision and scale for the target field. If the source fields do not have the same data type, the following error appears:
Lookup field <field name> in <file name> has conflict data types inferenced: <data type 1> and <data type 2>.
•You cannot include lookup fields of particular data types in a lookup condition. When the lookup field in a flat file has the Text or Ntext data type or the target field of a lookup has the Text or Ntext data type, the task fails.
•If you run a task with a lookup and the source field, lookup field, or output field of the lookup no longer exist in the lookup object, an error appears.