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 –
Hi Everyone, Can you suggest me, if I have an input file like –
Input file :-
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”
SELECT Id, GenderId,
WHEN GenderId=1 THEN ‘Male’
WHEN GenderId=2 THEN ‘Female’
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 ====================
Create table Test
================ Insert Query to insert data into Test table =================
Insert into test values (‘1′,’1’),(‘2′,’2’),(‘3′,’3’)
Scenario 1:- With SQL server – By using CASE statement
Input Query –
WHEN GenderId = 1 THEN‘Male’
WHEN GenderId = 2 THEN‘Female’
Scenario 2:- With SQL Server integration Services – By using SSIS Derived column transformation
- Create one SSIS package and give any name, say “Generate_Newcol”.
- Drag and drop Data Flow Task (DFT).
- 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).
- Now, drag and drop SSIS Derived Column Transformation and create a connection between OLEDB sources to Derived column.
- Double click on Derived Column Transformation. Finally it will look like below image.
Note down the things which needs to be done in the above Derived column Transformation editor.
- Expand columns.
- Provide Derived column name, say = Gender_Name.
- Derived column column = <add new column>.
- 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.
- If we are going to equal any value then we must use two equal symbols i.e. “==”.
- If we equal any string fields, we must enclose them within double quotes i.e. “”.
- 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.
- If everything is right with your expression then the expression will be highlighted in black color.
- 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.
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