This article explores the Character Map Transformation in SSIS package with available configurations.
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.
SELECT TOP (10) [ProductDescriptionID]
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.
It converts all text into lowercase. It is similar to SQL Lower function. Example: car becomes CAR.
It converts all text into uppercase. It is similar to a SQL UPPER function. Example: CAR becomes a car.
It reverses the order of byte of Unicode.
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
It converts Hiragana characters to Katakana characters.
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
This option performs reverse work of the half-width. It coverts half-width characters into full-width character.
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.
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.
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.
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.
- Refresh SQL Server Always On Availability Group databases using DBATools PowerShell - September 21, 2020
- Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups - September 17, 2020
- Backup SQL databases on the AWS S3 bucket using Windows PowerShell - September 16, 2020