Character Map Transformations in SQL Server Integration Services

By:   |   Comments   |   Related: More > Integration Services Data Flow Transformations


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.


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 SSISTransformation

CREATE TABLE Customer_Detail
   custName VARCHAR(500),
   custAddress VARCHAR(MAX),
   custcontact VARCHAR(10)

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

CREATE TABLE Customer_Detail_Output
   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.

ssis package

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.

ole db source

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

ole db source

Configure Character Map Transformation in Data Flow Task

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

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".

character map

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.

ole db destination

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

ole db destination

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

Below shows a successful execution of the package.

ssis package

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

query results

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips

Comments For This Article