Edit

Share via

Character Map Transformation

  • Article
  • 02/28/2023

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

The Character Map transformation applies string functions, such as conversion from lowercase to uppercase, to character data. This transformation operates only on column data with a string data type.

The Character Map transformation can convert column data in place or add a column to the transformation output and put the converted data in the new column. You can apply different sets of mapping operations to the same input column and put the results in different columns. For example, you can convert the same column to uppercase and lowercase and put the results in two different columns.

Mapping can, under some circumstances, cause data to be truncated. For example, truncation can occur when single-byte characters are mapped to characters with a multibyte representation. The Character Map transformation includes an error output, which can be used to direct truncated data to separate output. For more information, see Error Handling in Data.

This transformation has one input, one output, and one error output.

Mapping Operations

The following table describes the mapping operations that the Character Map transformation supports.

Operation Description
Byte reversal Reverses byte order.
Full width Maps half-width characters to full-width characters.
Half width Maps full-width characters to half-width characters.
Hiragana Maps katakana characters to hiragana characters.
Katakana Maps hiragana characters to katakana characters.
Linguistic casing Applies linguistic casing instead of the system rules. Linguistic casing refers to functionality provided by the Win32 API for Unicode simple case mapping of Turkic and other locales.
Lowercase Converts characters to lowercase.
Simplified Chinese Maps traditional Chinese characters to simplified Chinese characters.
Traditional Chinese Maps simplified Chinese characters to traditional Chinese characters.
Uppercase Converts characters to uppercase.

Mutually Exclusive Mapping Operations

More than one operation can be performed in a transformation. However, some mapping operations are mutually exclusive. The following table lists restrictions that apply when you use multiple operations on the same column. Operations in the columns Operation A and Operation B are mutually exclusive.

Operation A Operation B
Lowercase Uppercase
Hiragana Katakana
Half width Full width
Traditional Chinese Simplified Chinese
Lowercase Hiragana, katakana, half width, full width
Uppercase Hiragana, katakana, half width, full width

Configuration of the Character Map Transformation

You configure the Character Map transformation in the following ways:

  • Specify the columns to convert.

  • Specify the operations to apply to each column.

You can set properties through SSIS Designer or programmatically.

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

For more information about how to set properties, click one of the following topics:

Character Map Transformation Editor

Use the Character Map Transformation Editor dialog box to select the string functions to apply to column data and to specify whether mapping is an in-place change or added as a new column.

Options

Available Input Columns
Use the check boxes to select the columns to transform using string functions. Your selections appear in the table below.

Input Column
View input columns selected from the table above. You can also change or remove a selection by using the list of available input columns.

Destination
Specify whether to save the results of the string operations in place, using the existing column, or to save the modified data as a new column.

Value Description
New column Save the data in a new column. Assign the column name under Output Alias.
In-place change Save the modified data in the existing column.

Operation
Select from the list the string functions to apply to column data.

Value Description
Lowercase Convert to lower case.
Uppercase Convert to upper case.
Byte reversal Convert by reversing byte order.
Hiragana Convert Japanese katakana characters to hiragana.
Katakana Convert Japanese hiragana characters to katakana.
Half width Convert full-width characters to half width.
Full width Convert half-width characters to full width.
Linguistic casing Apply linguistic rules of casing (Unicode simple case mapping for Turkic and other locales) instead of the system rules.
Simplified Chinese Convert traditional Chinese characters to simplified Chinese.
Traditional Chinese Convert simplified Chinese characters to traditional Chinese.

Output Alias
Type an alias for each output column. The default is Copy of followed by the input column name; however, you can choose any unique, descriptive name.

Configure Error Output
Use the Configure Error Output dialog box to specify error handling options for this transformation.

Additional resources

Documentation