We can create the required expression using those built-in functions, Columns, Operators, and variables. SSIS Derived Column Transformation provides an option to choose whether you want to create new columns or replace existing columns with values calculated from expressions.
Demo 2 Split FullName into First and Lastname
Demo 3: How to write If Statement
Demo 4: Using operators
Demo 1:
Split text by delimiter.
I have an excel file with a column named FullAddress. I want to create 3 separate columns called Street, Post code and City
TOKEN(FullAddress,",",1)
Add Derived Column Task and write an expression.
Add Multicast to display the result and run the package.
Demo 2
Split FullName into First and Lastname
Firstname expression:
LEFT(FullName,FINDSTRING(FullName," ",1) - 1)
Lastname expression:
RIGHT(FullName,LEN(FullName) - FINDSTRING(FullName," ",1))
Demo 3:
How to write If Statement
Derived Column Expression:
LOWER(Status) == "s" ? "Single" : "Unknown"
Column Status in source table is a mix of lower and upper case. First I convert all the values to lower case, the check the condition
? stands for then
: stands for else
run the package
Step 2
If statement for all the values. I am converting the values coming from the excel file to varchar(10)
Expression:
(DT_STR,10,1252)LOWER(Status) == "s" ? "Single" :
LOWER(Status) == "m" ? "Married" :
LOWER(Status) == "d" ? "Divorced" :"Unknown"
Run the package
Demo 4:
Using operators.
&& AND
|| OR
(Gender == "M" && Status == "S") ? "Single male" :
(Gender == "F" || Status == "M") ? "Dangerous!" : "Democrat"
According this simple logic all married people are dangerous.
