SSIS - Character Map Transformation to convert data from Upper Case to Lower case and Vice-Versa.
In this article we will see how to use the Character Map transformation in SSIS. Character Map transformation is used to do some manipulation for the string type columns.
Lets understand how to use character map in ssis to convert data from Upper Case to Lower case and Vice-Versa.
Introduction
Character Map transformation can perform different operations on string type columns. We can either add a new column or we can do In-place change for that column.
Character Map can perform following operations on string type columns.
• Lowercase to Uppercase
Converts characters to uppercase.
• Upper to lower case
Converts characters to lowercase.
• Byte reversal
Reverses byte order
• Half width
Maps full-width characters to half-width characters.
• Full Width
Maps half-width characters to full-width characters.
Scenario:
We have table with sample data as source and we have to change the data of particular column into uppercase. Data having uppercase should be a new column. We have to get the output as Database table.
Solution
Source table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[characterMap data](
[Sr No] [INT] IDENTITY(1,1),
[Name] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [characterMap data] VALUES('Sam','usa')
GO
INSERT INTO [characterMap data] VALUES('johnson','brazil')
GO
INSERT INTO [characterMap data] VALUES('harry','usa')
GO
INSERT INTO [characterMap data] VALUES('jonathan','uk')
GO
INSERT INTO [characterMap data] VALUES('jason','canada')
GO
SELECT * FROM [characterMap data]
1. In SSIS, add data flow task in package and then add OLEDB Source as shown below
2. Configure OLEDB Source with appropriate table connection
Set database connection, Table and preview the data
3. As you can see, we have country column which is having all the lowercase data.
We need to change the Country column data from lowercase to Uppercase using Character-map.
4. Add Character-map from SSIS Toolbox.
5. Double click on Character-map to configure it
Select the particular columns which need to be changed as per our requirement
Here, I am selecting Country column to change the data to Uppercase. We can select any other operations as per our requirement shown in above image.
Note:
Using character map transformation, when we are doing operation, we can generate a new column as output or we can replace the old column with new data as output.
Refer below image to understand better
Click OK and Character-map transformation is configured.
6. Add OLEDB Destination from SSIS Toolbox and configure it
Map the columns from column tab.
Here, we are creating new column after using character map transformation to show the difference. We can replace the old column with new column also as show earlier
7. Run the package
Output:
As you can see, All the data of country is changed to Uppercase and a new column is created in output. We can do the same operation to convert data from lowercase to Uppercase also by selecting appropriate operation in Character-map configuration.
Conclusion
In this article, we learned how to use character map transformation in SSIS to change the data from Lowercase to uppercase and also vice versa condition.
Points to Remember
• Character-map transformation operation can be performed on columns which has String datatype
• Connections should be properly established.