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.
- Lowercase to Uppercase using SQL UPPER function
- Uppercase to Lowercase with SQL LOWER function
- 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.
|
1 2 3 |
SELECT TOP (10) [ProductDescriptionID] ,[Description] FROM [adventureworks2014].[Production].[ProductDescription] |
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.
In the Character Map, it provides the following 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
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: ｆｕｌｌ ｗｉｄｔｈ
- 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.
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.
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.
Click on Columns and remove the tick on ProductDescriptionID column. We want source column as description.
Join the ProductDescription task to Character Map, as shown below.
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
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.
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.
You can see the following symbol on the arrow for data viewer.
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.
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.
Re-execute the package, and it converts Uppercase operation for the input data. In the output, you can see all the characters in uppercase.
Let’s change the operation to Full Width and observe the output.
Click Ok and execute the SSIS package. It changes the characters font and spacing, as shown below.
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.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023