• Marek Vavrovic

Derived Column Transformation in SSIS

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.




14 views0 comments