Hello friends, today I am back with one more SSIS topic i.e. Character Map task in SSIS. This task is used for different type of conversion for string data type, like Upper case, lower case and many more. To implement Character map task, we will create two tables using below SQL script.
Create table EmpDetail(Eid int,Name nvarchar(100),Dept varchar(100))
Create table EmpDetailModified(Eid int,Name nvarchar(100),UCASEDept varchar(100))
Now we will insert one row into first table “EmpDetail” using below insert statement.
insert into empdetail values(101,N’搀攀洀漀’,’it’)
To implement the “Character map” task, we will take one “Data flow” task into control flow region(As shown below).
We will double click on the “Data flow” task and add one “OLEDB source” in data flow region(As shown below).
To configure the “OLEDB Source”, we will right click on it and click on “Edit” menu that will bring a new screen (OLEDB source editor), we will configure the “OLEDB source” and select the “Empdetail” table as a source table(As shown below).
Now we will take the “Character map” task and connect it with the “OLEDB source”(As shown below).
To configure the “Character map” task, we will right click on it and click on “Edit” menu that will bring a new screen(Character map transformation editor) as shown below.
We will select the Name and Dept columns as shown in the above screenshot. Now we will select “in-place change” option in destination field indicated by an arrow for both columns “Name” and “Dept”.In operation field, we will select “Traditional Chinese” for “Name” column and “Uppercase” for “Dept” column(As shown below).
We will click on “OK” button to save the settings that will close the above screen, now we will add “OLEDB Destination” task in our package and connect with the “Character map” task as show below.
We will configure the destination task by right click on it and select the “EmpDetailModified” table as destination table.once we are done with the configurations of “OLEDB Destination”, we are done with our package. Lets run it by pressing “F5” key and see the results.
Green color of the package is indicating that our package ran successfully. Lets check the both tables and see what has been transformed.
We can see in the above screenshot that value of dept column has been transformed into upper case and value of Name column has been transformed into “Traditional Chinese “characters. The way we have done Upper case conversion, similarly we can transform the characters into lower case by selection the operation “Lowercase“.Here I have explained only two type of conversion, apart from these two conversions below are the conversions which are supported by character map task.
You can explore rest of the conversions by yourself and please let me know if you have any question.
701 total views, 1 views today