Thursday 12 May 2016

Character Map Transformation in SSIS

Suppose we can to convert our column value to lower to upper case. It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Character map transformation is used to make changes to string type columns only.

Operations available here are:

Ø  Lowercase
Ø  Uppercase
Ø  Byte reversal
Ø  Hiragana
Ø  Katakana
Ø  Half width
Ø  Full width
Ø  Linguistic casing
Ø  Simplified Chinese
Ø  Traditional Chinese
 
Lowercase:- 
Name itself, it convert column value to Lower case. For Example column value is BageshKumarSingh is converted as bageshkumarsingh.
Uppercase:-
Name itself, it convert column value to Upper case. For Example column value is BageshKumarSingh is converted as BAGESHKUMARSINGH.
Byte Reversal:-
Reverses the bytes order of the Unicode.
Hiragana:-
Converts Katakana characters to Hiragana characters.
Katakana:-
Converts Hiragana characters to Katakana characters.
Half width: -
Converts Full-width characters to Half-width characters.
Full width:-
Converts Half-width characters to Full-width 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.
Simplified Chinese: -
Maps traditional Chinese characters to simplified Chinese characters.
Traditional Chinese: -
Converts simplified Chinese characters to traditional Chinese characters.

Implementation of Character mapping Transformation

Open the SSDT
Take Data flow task.
I am taking OLEDB source. Create a connection.
  

Now I am taking Character map transformation.
Double click on that transformation.
Character Map transformation Editor Window will be open.
  


Select the Column which one you want to convert.

Input Column:
 Whatever we selected in the Available Input Columns option will automatically reflected in this option. We can also directly select input column here itself.
  

Destination:
 We have an option to select, whether we want to replace the original column or we want to add this as a new column. In this example we are using new column option.
  


Operation: 
We can perform below operation.
  

We describe details on above
Output Alias: 
Specify the new column name. It acts same as ALIAS column in SQL
  

Like for First FirstName I made Output Alias AS Lower_FirstName.

After that click ok.
Now I am taking multicast transformation.
For seeing the result I am taking Data Viewer.
  

Now I am executing the package.
  



No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts