By default, mappings replace all special characters in field names with an underscore (_). You can configure a mapping to preserve some special characters in field names for flat file, MySQL, Oracle, and SQL Server connections.
When you configure a mapping to preserve special characters in field names, the mapping preserves most, but not all, characters. The following table describes how special characters are handled for different types of transformations:
Transformation type
Special character handling
Source transformation
The following characters are replaced with an underscore:
" ; : , \t \r \n
All midstream transformations
The following characters are replaced with an underscore:
User-defined expressions can't use field names that include the number sign (#). For example, the expression in an Aggregator transformation or Expression transformation can't include the number sign.
Lookup transformation
The following characters cause the mapping to fail:
Normalized field names can't include the number sign.
Target transformation
The following characters are replaced with an underscore:
. " ; : , \t \r \n
If a target field name includes a number sign, the Target transformation must use automatic field mapping.
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.
•Mappings in advanced mode and SQL ELT mode don't have the option to preserve special characters.
•After you enable the property to preserve special characters, the field names update in the Source and Target transformations but not in midstream transformations. Ensure that the field names in midstream transformations are consistent with the source and target field names.
For example, you use a source file that has a field named F·Name in mapping with a Source tranformation and an Expression transformation. By default, the mapping replaces the middle dot with an underscore, and the field name becomes F_Name in both transformations. When you enable the property to preserve special characters, the field name in the Source transformation updates to F·Name, but the Expression transformation still uses F_Name. You manually update the field name in the Expression transformation to match the name in the Source transformation.
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 configure 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 the value to Yes.
To configure 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 Custom Properties and set the value to AddQuotesAlways=Yes.
dIn the Secure Agent, ensure that the DTM property UseCustomSessionConfig is set to the value 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.