When it comes to string formatting in SSIS, “Character Map” transformation task is one of the very useful tasks. It generally used to convert data to Uppercase, lowercase, byte reversal etc. let us see one small demo for the same.
Before we start our package development, let us first create one small table in SQL Server and insert some data into it by following TSQL Script.
create table Employee
insert into Employee
select ‘rITesH’,‘ShAh’ union all
Now, create one new project in BIDS (Business Intelligence Development Studio) for SSIS package. Drag “Data Flow” task on your “Control Flow” tab and double click on that “Data Flow” to configure it which will forward you to “Data Flow” tab.
Drag “Ado Net Source” from tool box and drop it into your “Data Flow” tab. Double click on “Ado Net Source” to configure it and select your Server, database and table, “Employee” table in our case.
Now, drag “Character Map” transformation task to your work area and join it with extended green arrow coming from “Ado Net Source”. Now double click on “Character Map” transformation task to configure it. We are going to convert FirstName column to UPPERCASE and LastName column to LOWERCASE. Look at screen capture below for more information.
Once you finish configuring “Character Map” transformation task, drag “Ado Net Destination” from tool box to right below “Character Map” transformation task and join it with extended green arrow coming from “Character Map” transformation task.
Double click on “Ado Net Destination” to configure it and select your SQL server, Database and table (we are going to use same Employee table as destination too) .
Once you finish all these steps, you are ready to launch your package. Hit F5 to run the package and see EMPLOYEE table in SQL Server whether it has two more rows with different case of alphabet.