Learn SSIS Derived Column Transformation

12
1045

Aim :- Earlier tutorials were targeted to provide insight on various transformations in SSIS. Continuing with the same approach, today we are going to learn one more SSIS Transformation named as Derived Column Transformation. We fill follow our traditional method i.e. Learning things via doing them practically. This always gives a better vision of the concept.

Description :-  Read this loudly – SSIS Derived Column Transformation. The name itself indicates that it is going to derive something from column(s). If this thing came to your mind then you hit the bulls eye. It will create a new column or replace existing column by writing an expression in Derived column editor. The expression may contain functions like Number, string, date, etc. and also we can use the variables.

Let’s understand this with an example – Suppose, we have three input columns like First_name, Middle_name and Last_name. Finally, I want only one column as an output column i.e. Full name. We can achieve this by concatenating the three input columns (First_name, Middle_name, Last_name).

But now the problem is how we can concatenate these three input columns. So, the solution to this problem is Expressions i.e. we can write a following expression (Expression starts with equal (=) sign):- =First_name+” ”+Middle_name+” ”+Last_name

NOTE  In above Expression, we are using “+” symbol for concatenation.

Recently someone asked us one question on Facebook which is quoted as below –

QUESTION

Hi Everyone, Can you suggest me, if I have an input file like –

Input file :-
Id            Genderid 
1                      1
2                      2
3                      3

Then, how can we show an output like below?

Output file :-
Id   GenderId     Gender_Name
1              1                     Male
2              2                    Female
3              3                    Unknown

Let us make this question clearer (Question modified a little bit for more understanding) –

While moving the input data into a table, user wants a new column (i.e. Gender_name) by referencing to the column “GenderID” which means –

  • “1” as Male,
  • “2” as “Female”, and
  • “3” as “Unknown

ANSWER

SELECT Id, GenderId,
newcol =
CASE
WHEN GenderId=1 THEN ‘Male’
WHEN GenderId=2 THEN ‘Female’
ELSE ‘Unknown’
END
FROM   <Tablename>

We will answer this question in two ways –

Scenario 1:- Here, I will show how we can achieve the output with SQL Server.

Scenario 2:- Here, I will show how we can achieve the same output via SSIS (By using SSIS Derived column transformation)

Before we begin our in depth study on 2 above scenarios, firstly let’s create a table.

================= Create Query to create Test table ====================

Use PhPRing
Go

Create table Test
(Id varchar(10),
GenderId varchar(10))
Go
================ Insert Query to insert data into Test table =================

Insert into test values (‘1′,’1’),(‘2′,’2’),(‘3′,’3’)
Go

Scenario 1:- With SQL server – By using CASE statement

Input Query

SELECT Id,
GenderId,
Gender_Name=CASE
WHEN GenderId = 1 THEN‘Male’
WHEN GenderId = 2 THEN‘Female’
ELSE‘Unknown’
END
FROM   Test

Output

Scenario 1 - With CASE statement in MS SQL Server
Scenario 1 – With CASE statement in MS SQL Server

Scenario 2:- With SQL Server integration Services – By using SSIS Derived column transformation

  1. Create one SSIS package and give any name, say “Generate_Newcol”.
  2. Drag and drop Data Flow Task (DFT).
  3. Double click on Data Flow Task. Now, drag and drop OLEDB source and create connection to your database say, PhpRing in this example. (I hope you all know how to create a connection to the database).
  4. Now, drag and drop SSIS Derived Column Transformation and create a connection between OLEDB sources to Derived column.
  5. Double click on Derived Column Transformation. Finally it will look like below image.

SSIS Derived column Transformation Editor

Note down the things which needs to be done in the above Derived column Transformation editor.

  1. Expand columns.
  2. Provide Derived column name, say = Gender_Name.
  3. Derived column column = <add new column>.
  4. Write an Expression as = [GenderId]==”1″?”Male”: [GenderId]==”2″?”Female”:”Unknown”

Explanation about the expression – This expression will also work similar like CASE statement/IF statement. I hope you all know how IF works in reality. In case you are not sure, refer below line to understand the functionality of IF statement.

“If something equal to some value, do like this else do like other”.

Below are the Things to remember when writing an expression in SSIS Derived Column transformation.

  1. If we are going to equal any value then we must use two equal symbols i.e. “==”.
  2. If we equal any string fields, we must enclose them within double quotes i.e. “”.
  3. If there is anything wrong with your expression then the expression will be highlighted in Red color and we all very well know that red color indicates an error.
  4. If everything is right with your expression then the expression will be highlighted in black color.
  5. If you are writing an expression in editor and are using any columns then, just drag & drop those columns in your expression editor. This will avoid manual type errors such as column names mismatch, etc. As, Column names are case sensitive so expression may not be valid if column names are not written exactly with proper casing.

This is all about the two scenarios. Now, I am moving ahead and will execute my SSIS Derived Column Transformation package. Let’s see what will happen now.

Scenarion 2 - With Derived Column Transformation in SSIS
Scenarion 2 – With Derived Column Transformation in SSIS

We can see in the above image that column Gender_Name is created and the result set is exactly similar to Scenario 1 result.

This is the end of the article. I hope you all enjoyed this quick post on Derived Column Transformation in SSIS. Kindly share feedback and doubts as comments below. If you have any questions which you want to discuss practically, just ask below in the form of comment or you can post it on our Facebook page too.

Editor – Chander Sharma

View articles written by Chander Sharma

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.5/10 (16 votes cast)
VN:F [1.9.22_1171]
Rating: +14 (from 14 votes)
Learn SSIS Derived Column Transformation, 9.5 out of 10 based on 16 ratings

12 COMMENTS

  1. This is really a great read for me.
    Thank you for publishing such nice articles really great insightful post.
    This stimulates me to check more often for new write ups.
    Keep posting Avinash !!

Comments are closed.