Character Map Transformations in SSIS

By: Bhavesh Patel | Updated: 2018-11-14 | Comments | Related: More > Integration Services Data Flow Transformations

Problem

The SQL Server Integration Services (SSIS) Character Map transformation applies string functions, such as a conversion from lowercase to uppercase for character data. This transformation works only on columns which have a string data type. As per the transformation output, it creates a new column or changes the converted data into the existing column. This transformation gets one input, then returns one output and one error output. In this article, we will see how to use the Character Map transformation in an SSIS package.

Solution

As a part of the Character Map transformation, it gives options on whether the output result will be overridden (in place change) or introduce a new column. I have listed some of the options that are available in this transformation.

Byte reversal
Lowercase
Uppercase
Hiragana
Katakana
Half width
Full width
Traditional Chinese
Simplified Chinese
Linguistic casing

As per MSDN, below is a short description of each.

Value	Description
Byte reversal	Convert by reversing byte order.
Lowercase	Convert to lower case.
Uppercase	Convert to upper case.
Hiragana	Convert Japanese katakana characters to hiragana.
Katakana	Convert Japanese hiragana characters to katakana.
Half width	Convert full-width characters to half width.
Full width	Convert half-width characters to full width.
Traditional Chinese	Converts simplified Chinese characters to traditional Chinese characters.
Simplified Chinese	Converts traditional Chinese characters to simplified Chinese characters. For instance, how are you in Simplified Chinese is
Linguistic casing	Apply linguistic rules of casing (Unicode simple case mapping for Turkic and other locales) instead of the system rules.

Now I will demonstrate how to use the Character Map in SSIS.

Setup SQL Server Test Environment

I have prepared a sample script and after applying this script in your test environment database "SSISTransformation" will contain the Customer_Detail and Customer_Detail_Output tables. We will use the Customer_Detail as input into the package and update the Customer_Detail_Output table with the converted data.

USE MASTER
GO

CREATE DATABASE SSISTransformation
GO

USE SSISTransformation
GO

CREATE TABLE Customer_Detail
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   custName VARCHAR(500),
   custAddress VARCHAR(MAX),
   custcontact VARCHAR(10)
)
GO

INSERT INTO Customer_Detail
SELECT 'Test','B-777 Vaishnodevi, Ahmedabad','1212121212'
UNION ALL
SELECT 'Domy','B-333 Gota, Ahmedabad','1212121213'
GO

CREATE TABLE Customer_Detail_Output
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   custName VARCHAR(500),
   custAddress VARCHAR(MAX),
   custcontact VARCHAR(10)
)

Demonstration of Character Map Transformation in SSIS

After applying the above script, my database is ready for SSIS. I have a transaction table "Customer_Detail". I need the data in uppercase format, so I will show how this can be done using the Character Map transformation.

First Configure Data Flow Task

In the Control Flow I have added a Data Flow Task named "CharacterMapTransformation". After creating, double click on this new task.

Configure Source Connection in Data Flow Task

In order to get data from table Customer_Detail, I am going to add an OLE DB Source.

I created a connection to my database and I configured the OLE DB Source as follows.

Configure Character Map Transformation in Data Flow Task

Now, I am going to add and configure the Character Map.

As per MSDN, I have described the character map transformation editor here.

Available Input Columns - Use the check boxes to select the columns to transform using string functions. Your selections appear in the table below.
Input Column - View input columns selected from the table above. You can also change or remove a selection by using the list of available input columns.
Destination - Specify whether to save the results of the string operations in place, using the existing column, or to save the modified data as a new column.

Value	Description
New column	Save the data in a new column. Assign the column name under Output Alias.
In-place change	Save the modified data in the existing column.

Here is how the Character Map Editor looks. I selected the destination column as "In-Place Change" instead of "New Column".

Configure Destination Table in Data Flow Task

After applying the conversion, I need to insert the data into the destination table, so I added an OLE DB Destination as follows.

Below shows the mapping from the Character Map to the Destination.

After doing the above steps my package is ready for execution.

Below shows a successful execution of the package.

I can see the final results in my Customer_Detail_Ouput table after running the package.

As alternative we could use a SQL function in the script directly, for example I could have used the UPPER/LOWER SQL function. As you can see there are several ways of doing things in SQL Server and you can add this to your list options.

Next Steps

Check out Transformations in SQL Server Integration Services.
Check out Integration Services Transformations in MSDN.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-11-14

Character Map Transformations in SQL Server Integration Services