Character Map Transformation in SSIS 2008R2 with Example

Friends,

In this post we are gonna discuss about Character Map Transformation in SSIS 2008R2 with Example. Character map transformation is used to make changes to string type columns only. String functions can be applied using this transformation like upper case, lower case, byte reversal and so on. To understand in detail let’s take one data flow task and add character map transformation as per below screen print.

In this transformation we have to select the column. Note: we can only use string type columns in this transformation.

Here as per screen print we can select any string type column in InputColumnoption. Either we can select from drop down or we can select through check box in Available Input columns window.

Once that is done we have option to select whether output of this transformation should be a new column or on same column we want the change. We can use New Column or In-Place change as per our requirement.

Then we have to choose operation, we can select from drop down what operation we want to perform.

As per the list we can see we have uppercase, lowercase, byte reversal etc operations available.

Please find below all operation details of Character Map Transformation in SSIS.

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.

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

The above given operations are Mutually exclusive and the list of the same is given below.

(Source: MSDN)

Let’s see one example

Uppercase:

Here as per our selection, we will apply Uppercase operation on AccountDescription column as In-Place change so no new column will be generated, we will see changes on same column.

Click on OK and Execute. I added two data viewers one before the character map and another after the same to show how the data transforms. In Before character map data viewer we can see Account description column’s data.

Let’s see after character map transformation data for that column. We can see everything is in uppercase.

Same way we can use different transformations.

Hope this helps you guys .. Give a try ..