By default, mappings replace all special characters in field names with an underscore (_). You can configure a mapping to preserve some special characters in the source and target field names for flat file, MySQL, Oracle, and SQL Server connections.
When a mapping preserves special characters, the source and target field names can include any character except the following characters: . + - = ~ ` ! $ % ^ & * ( ) [ ] { } ' \ " ; : / ? , < > | \t \r \n
The mapping replaces the listed characters with an underscore.
When you preserve special characters, consider the following rules and guidelines:
•The first character of a field name must be an alphanumeric character or an underscore.
•In an Expression transformation, field names can include only alphanumeric characters and the following special characters:
- Commercial at (@)
- Dollar sign ($)
- Fullwidth commercial at (＠)
- Fullwidth dollar sign (＄)
- Fullwidth low line (＿)
- Fullwidth number sign (＃)
- Middle dot (·)
- Number sign (#)
- Underscore (_)
•User-entered expressions can't use field names that include the number sign. For example, the aggregator expression in an Aggregator transformation and the expression in an Expression transformations can't include the number sign.
•In a Normalizer transformation, normalized field names can't include the number sign.
•If a target field name includes a number sign, the Target transformation must use automatic field mapping.
•In a Lookup transformation, field names can't include single-byte special characters.
•When you enable the property to preserve special characters, the field names change in the applicable Source and Target transformations but not in midstream transformations. After you enable the property, ensure the field names in midstream transformations are consistent with the source and target field names.
•You can't preview a mapping that includes special characters in field names. The preview job will fail.
•Mappings in advanced mode and SQL ELT mode don't have the option to preserve special characters.
Preserving special characters in native field names
To preserve special characters, configure the mapping advanced properties and DTM properties.
1In the Mapping Designer, click Actions > Advanced Properties and enable the property Preserve special characters in native field names.
2Configure the DTM property AddQuotesAlways in the Secure Agent or the mapping task.
To set the property in the Secure Agent, perform the following steps:
aIn Administrator, locate the agent on the Runtime Environments page and click Edit.
bIn the Custom Configuration Details section, select Data Integration Server as the service and DTM as the type.
cSet the name to AddQuotesAlways and set the value to Yes.
To set the property in the mapping task, perform the following steps:
aIn Data Integration, edit the mapping task.
bIn the Advanced Session Properties section on the Runtime Options page, add a new session property.
cChoose the property name advanced.custom.property and set the value to AddQuotesForLookup=Yes.
3If the mapping includes a Lookup transformation, configure the DTM property AddQuotesForLookup and set the value to Yes. Follow the instructions in step 2 to configure the property in the Secure Agent or the mapping task.