Rajendra Gupta
Add SQL Multicast operator

Character Map Transformations in SSIS packages

September 11, 2019 by

This article explores the Character Map Transformation in SSIS package with available configurations.

Introduction

In the previous article, we explored SQL Server commands to change text case in the following scenarios.

  1. Lowercase to Uppercase using SQL UPPER function
  2. Uppercase to Lowercase with SQL LOWER function
  3. Change the first character of each word in a string to a capital letter. We do not have any available function in SQL Server to do that. We performed this task using a custom function T-SQL code

As you can see here, we have limited functions in SQL Server to convert the character case transformations. It is possible, but you should be a T-SQL programming expert and able to write complex queries.

You might have heard a famous proverb – A FRIEND IN NEED IS A FRIEND INDEED.

You might be thinking about the relationship of this proverb with this article. I use this quote for the true friend of DBA and Developers in SQL Server – SSIS Package. Yes, we can easily do character case transformations using SSIS packages. We use Character map transformation in SSIS package to achieve this task.

Let’s explore this transformation in this article in details.

Pre-requisites for this article

  • SQL Server instance with integration services
  • SQL Server Data tools
  • Sample database – AdventureWorks

In the sample database AdventureWorks, we have the following data in the [ProductDescription] table.

Sample data

We will use data in description column as a source in this article.

Overview of Character map transformation in SSIS package

In SSIS Toolbox, we have the following Character Map transformation.

Character Map transformation

In the Character Map, it provides the following mapping operations.

mapping operations

Let’s get a brief overview of each operations in character map transformation.

Lowercase

It converts all text into lowercase. It is similar to SQL Lower function. Example: car becomes CAR.

Uppercase

It converts all text into uppercase. It is similar to a SQL UPPER function. Example: CAR becomes a car.

Byte reversal

It reverses the order of byte of Unicode.

Hiragana

It converts Katakana characters to Hiragana characters.

  • Katakana and Hiragana both are characters in a Japanese syllabary
  • In the following image, you can see both characters

Katakana characters to Hiragana characters

Katakana

It converts Hiragana characters to Katakana characters.

Half-width

We use this option to convert full-width character to half-width character. In full-width character, you have the same width for each character and similar to Chinese character. It does not depend upon the character set. Half-width characters occupy half-space compared to full-width characters.

  • Full-width example: full width
  • Half-width example: fullwidth

Full width

This option performs reverse work of the half-width. It coverts half-width characters into full-width character.

Linguistic casing

    Usually, SQL Server uses system language to insert data into tables. Example: date format. If your system uses date format in US format, SQL also uses the same format by default. We can use Linguistic casing to use other local languages.

Simplified Chinese

We can use this option to convert traditional Chinese characters to Simplified Chinese characters.

  • Traditional Chinese characters: These are the traditional Chinese character and do not have any changes (new character addition or existing character deletion) from 1946
    • Example: My name is Rajendra becomes 我的名字是拉金德拉 in traditional Chinese
  • Simplified Chinese: These characters are standardized Chinese characters
    • Example: My name is Rajendra becomes 我的名字是拉金德拉 in simplified Chinese

Note: I used Google translate to show these conversions.

Traditional Chinese

It converts simplified Chinese characters to Traditional Chinese characters.

  • Example: Converts text (My name is Rajendra) 我的名字是拉金德拉 to 我的名字是拉金德拉

We can use multiple options for a column using Character Map transformation in SSIS package. There are few exclusions as well.

Exclusion in Character Map Transformation in SSIS package

The simplest example is lowercase and uppercase. Suppose we used lowercase to convert all characters into lowercase. We should not use an uppercase option in this case as it is opposite to the lowercase option. In the following image from Microsoft docs, you can find mutually exclusive operations.

Exclusion in Character Map Transformation in SSIS package

Configure Character Map Transformation in an SSIS package for

Let’s create an ETL to use Character Map transformation in SSIS package.

Drag a Data flow task in the control flow area and rename it as Character Map Transformation in SSIS.

Data flow task in SSIS package

In the data flow tab, add an OLE DB source and configure a connection to point our source database Adventureworks. Rename this OLE DB source to ProductDescription.

Change data access mode to SQL command and specify SQL query in this.

Sepcify connection and SQL command

Click on Columns and remove the tick on ProductDescriptionID column. We want source column as description.

Select required column

Join the ProductDescription task to Character Map, as shown below.

Join the ProductDescription task to Character Map

We need to configure the appropriate options in the Character Map. Double click on it, and it opens the Character map transformation editor. In the transformation editor, it shows you all available columns in the source table. Let’s do the following configurations.

  • Select the column name description in the available input columns table
  • Select destination as New Column. We can replace the existing column as well by selecting option In-place change. It is good to use a new column so that we can compare the original and converted text at one place
  • Select operation as lowercase. We want to convert all text into a lower case character
  • In the output alias, we can specify the name of a new column after case conversion. I specified the new column as Description_new

Select the operation and new column name

You can add an OLE DB destination to get the data into a SQL Server table. For this demo, we want to view the behavior of Character map Transformation in SSIS package. Let’s add a SQL multicast and join it with the character map as shown below.

Add SQL Multicast operator

Right-click on the arrow between Character Map and Multicast and enable data viewer. You can view the data after the character map transformation using this data viewer.

enable data viewer

You can see the following symbol on the arrow for data viewer.

data viewer symbol

Execute the SSIS package from the Start button or press F5. In the data viewer, you can see column description (original) and description_new(new) in lowercase. All characters in description_new column are in lowercase.

Character map transformation output as lowercase

Let’s try a few other operations as well. We cannot do this while the package is executing. First, Stop the SSIS package execution (press Shift + F5) and change the operation as Uppercase as shown in following the image.

Change operation as Uppercase

Re-execute the package, and it converts Uppercase operation for the input data. In the output, you can see all the characters in uppercase.

Character map output as Uppercase

Let’s change the operation to Full Width and observe the output.

Change operation as full width

Click Ok and execute the SSIS package. It changes the characters font and spacing, as shown below.

Character map output as Full width

Select multiple options as lowercase and uppercase

As highlighted earlier, we cannot use two operations that are not compatible with each other. For example, let’s select both lowercase and uppercase operations together, and you get the following error message.

Select multiple options in character transformation

Similarly, we cannot use Uppercase and half-width operation together on the same column. You can refer the table shown in the above section for the mutually exclusive operations.

Select multiple options as uppercase and half-width

Conclusion

In this article, we explored Character Map Transformation in SSIS package to convert the case of the characters using SSIS. You can easily change the configuration from the available operations and get the desired output.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
270 Views