SQL Server Integration Services (SSIS) - Character Map (Lower to Upper) Transformations in SSIS

Introduction:

In this article we are going to see how to use the Character Map transformation control in SSIS Packaging. We are going to see an example of how to do a transformation of lower to upper case of a column using the character map transformation control. Let's jump start to the section on how to do that using a sample package.

To follow my series of articles on SSIS packages, please refer to my profile.

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Character Map control. Once you open the project just drag and drop the Character map control as shown in the screen below:



Before configuring the controls we need to make sure of which process we are going to follow to do the transformation. Here we are going to take 2 tables as source and destination in the same database and do some transformations to check how exactly the process is used for. 
I have created a table as shown below:

CREATE TABLE EmpTable 
(
EMPID INT, 
EMPFname VARCHAR(50), 
EMPLnmae VARCHAR(50)
)
Go

INSERT INTO EmpTable (EMPID, EMPFname, EMPLnmae)VALUES (1,'karthik','karthik')
INSERT INTO EmpTable (EMPID, EMPFname, EMPLnmae)VALUES (2,'arun','arun')
INSERT INTO EmpTable (EMPID, EMPFname, EMPLnmae)VALUES (3,'mani','mani')

Now I have created a destination folder as shown below:

CREATE TABLE EmpDestination 
(
EMPID INT, 
EMPFname VARCHAR(50), 
EMPLnmae VARCHAR(50)
)

Now for our process we are going to make a transformation of converting the lower case to upper case; let's see how to do that.

To configure the Character Map just double-click on the control and select the columns to be added for the transformation and select the necessary transform as shown in the image below:



Now drag and drop a destination oledb provider and connect to the destination table and map it as shown in the screen below:



Once everything is configured your package screen looks like below:



Now press F5 to run the package. Once the package gets executed it will look like the screen below:



Now the package is executed successfully; to check that the transformations have completed successfully, go to the query analyzer and run the query as shown in the screen below:

Conclusion:

So in this article we have seen how to use the Character Map to transform characters from lower to upper case for a particular columns in a table.