Tuesday, August 18, 2015

Character Map Transformation

Character Map Transformation


- The Character Map transformation enables us to modify the contents of character-based columns
- Modified column can be created as a new column or can be replaced with original one
- The following character mappings are available:
--> Lowercase : changes all characters to lowercase
--> Uppercase : changes all characters to uppercase
--> Byte reversal : reverses the byte order of each character
--> Hiragana : maps Katakana characters to Hiragana characters
--> Katakana : maps Hiragana characters to Katakana characters
--> Half width : changes double-byte characters to single-byte characters
--> Full width : changes single-byte characters to double-byte characters
--> Linguistic casing : applies linguistic casing rules instead of system casing rules
--> Simplified Chinese : maps traditional Chinese to simplified Chinese
--> Traditional Chinese : maps simplified Chinese to traditional Chinese

EXAMPLE

Pre-requisite

Following script has to be created in DB


CREATE TABLE CharacterMapDemoSource (CID INT, LowerCaseVARCHAR(50), UpperCase VARCHAR(50), Byte NVARCHAR(50))
Go
INSERT INTO CharacterMapDemoSource (CID, LowerCase, UpperCase,Byte)
VALUES (1,'abc','ABC','demo'),
(2,'abC','AbC',N'搀攀洀漀')
Go
CREATE TABLE CharacterMapDemoDestination (CID INT, LowerToUpperVARCHAR(50), UpperToLower VARCHAR(50), ByteReversal NVARCHAR(50))
Go


Steps

1. Add Data Flow Task in Control Flow
2. Drag source in DFT and it should point to CharacterMapDemoSource Table.
3. Drag Character Map Transformation and do following settings.

4. We can add new column or we can replace existing one with new Output Alias
5. For each column, we can define different operation
6. Drag Destination and connect it with Character map
7. Records will look something like this.
8. ByteReversal operation basically goes by the byte of a string and reverses all the bytes.