Use of Derived column in SSIS

Introduction

SSIS has many transformations tasks and Derived column is one of them.

In this article we will try to do a sample in which we will try to use a derived column in deriving a new column at runtime and use it. Let's say we have a file in which we have Salary and Bonus as two fields and we need to calculate the gross salary, or to change the name into upper case or do some sort of calculations then we go with the derived columns.

Scenario, We have a file containing name, salary and bonus as three fields and we will use our SSIS package to generate a new file which will have derived field Gross salary and display the name filed in capital letters.

So Lets Start...

1. Open MS BI Development studio. Select an Integration Services Project that will load Package1.dtsx

2, Drag and Drop a data flow task as shown.

3. Double click on the data flow task, And place a Flat file source, a derived column transformation task and a flat file destination task.

4. Configure the flat file source by double clicking that, Create a flat file source connection and select a sample input file path, Preview your columns by clicking the columns tab. Gotot advanced and change the datatype to integer for column 3 and column 4 And then click ok.

5. Similary configure the Flat file destination task.

6. Double click on derived column transformation task and configure it to derive the gross salary and Name with upper case columns

In the derived transformation editor, select the replace column1 [Since Column1 is for name field] from the Derive column drop down. Drag drop the Upper expression from the string functions and use it as shown.

7. Smilarly derive another column for Gross Amount. Next double click on Destination file and map the columns to the output file. Click Ok

8. Before we run the package we need to put the input file in the input location. then Execute the package.

9.See the output file.

Thats it.. Simple

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.