.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

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

Posted By:Karthikeyan Anbarasan       Posted Date: April 30, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we are going to see on how to use the Character Map transformation control in SQL Server Integration Services (SSIS) Packaging.
 

Introduction:

In this article we are going to see on how to use the Character Map transformation control in SSIS Packaging. Character Map transformations are used to do some formatting for the columns based on users selection. The formatting can be like transforming Lower to upper case, Upper to lower case, Byte reversal, half width, Full Width etc. These transformations are used in the data flow process to maintain some standards across the application and to save some issues in the log based on the application name search. Let's jump start into the example to see on how to use this control. 


You can also read my other articles on SSIS 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 (1,'KARTHIK','KARTHIK')


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 upper case to lower 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 Upper to Lower case for particular columns in a table.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend