SQL Server Integration Services (SSIS) – Part 40 – Character Map (Lower to Upper) Transformations in SSIS

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 18663 red flag
Rating: 4.4 out of 5  
 5 vote(s)

This is part 40 of the series of article on SSIS. In this article we are going to see on how to use the Character Map transformation control in SSIS Packaging.

Introduction:

In this article we are going to see on how to use the Character Map transformation control in SSIS Packaging. We are going to see an example on 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 click here.

Steps:

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



Before configuring the controls we need to make sure on 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 our process we are going to make a transformation on converting the Lower case to Upper case, let’s see on 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 below image



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



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 below screen



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



Conclusion:

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

Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Posted by: Pradsir on: 4/22/2011 | Points: 25
Hi Karthikeyan Anbarasan

While doing same I got error for EMPID being int;

Then i changed the same to text;

I think is there any issue with int in the case of character map..

As always very nice Article..

Thanks
Posted by: Karthikanbarasan on: 4/22/2011 | Points: 25
Hi Pradsir,

Thanks for your response.

It would def get an error when you try to do the character map set for an integer. If you go through my sample you can see that i selected first name and last name alone for the mapping set and i left Emp id unchecked since its an integer. You no need to change the datatype to text rather you can keep it as integer itself and make it unselected.

Hope you got the difference.

Let me know in case of u have any doubts
Posted by: kjreddy416-20750 on: 2/11/2013 | Points: 25
hai sir i am fresher i joined msbi course so i want full msbi book material please send me this is mail id: kjreddy416@gmail.com
Posted by: kjreddy416-20750 on: 2/11/2013 | Points: 25
hai sir i am fresher i joined msbi course so i want full msbi book material please send me this is mail id: kjreddy416@gmail.com

Login to post response

Comment using Facebook(Author doesn't get notification)