Search
Sponsor
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Tue, 25-Nov-2014 Authors
All Time Authors
Sourav.Kayal
39750
SheoNarayan
38050
Niladri.biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

Use of Derived column in SSIS

Madhu.b.rokkam
Posted by under Sql Server category on for Beginner level | Points: 250 | Views : 34715 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
In this article we will go through the functionality 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

Hope you all liked it. Please comment on it.
Page copy protected against web site content infringement by Copyscape
About the Author

Madhu.b.rokkam

Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu
http://www.dotnetfunda.com

Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)