Floating FB popout byF5debug

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

 

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.

You can look into my series of article on SSIS at the url -http://f5debug.net/tutorial/ssis.aspx

[more]

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

clip_image001

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))GoINSERT 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')

clip_image002

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

clip_image003

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

clip_image004

Once everything is configured your package screen looks like below

clip_image005

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

clip_image006

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

clip_image007

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

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter