Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Character Map Transformations in SQL Server Integration Services


By:   |   Last Updated: 2018-11-14   |   Comments   |   Related Tips: 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.

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.


Last Updated: 2018-11-14


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools