Ravi Khanal

Scenario:
I have file in different heterogeneous sources like Text file and Excel File and I want to merge it into the SQL Server Table. But before loading the data into SQL Server table I have to change the First Name Column into Upper Case and also I need to give 20% raise in the salary. So I should have the proposed salary column added to the table.

Solution:

So First I need to have the data flow task for this.

Then in the data flow the package would be like this:

Let us first add the flat file connection Manager, Excel Connection Manager, and Oledb Connection Manager.

In the flat file Source add the Flat file connection Manager.

In the Excel Source add the Excel Source connection Manager.

Since the data in Excel is in Unicode format so it should be converted into string format using Data Conversion Transformation.

Before the data is merged, it should be sorted. So lets sort the Flat file Source and converted data of Excel by First Name and Last Name.

Then after Sorting the data, it should be merged.

Then after merging the data, the first name column should be changed to uppercase. So the Character Map Transformation should be used.

After changing the data into uppercase, we have to add a new Salary column which could show the raise in the salary. So the derived column transformation has to be used which will calulate the 20% increase in salary.

Then finally the OLEDB destination is added where all the data is dumped. In this OLEDB Destination the OLEDB Connection manager is added and the desired table is selected and mapped.