Derived Column Transformation & Data Conversion Transformation

- It will allow us to create new columns based on some formula
- We can use existing columns / any function and build expression for new derived column
- Derived column can be added / replaced in place of existing
-


EXAMPLE

Pre-requisite

CREATE TABLE DerivedColumnDemoSource (ID int, FirstName varchar(10), LastName varchar(10), DOB Datetime, Expense int, Income int)

GO

INSERT INTO DerivedColumnDemoSource (ID, FirstName, LastName, DOB, Expense, Income)

VALUES

(1,'Nisarg','Kinariwala','10/09/1983', 100,200),

(2,'Megha','Shah','07/12/1986', 50,250),

(3,'Swara','Desai','01/01/2005', 300,50)

Go

CREATE TABLE DerivedColumnDemoDestination (FullName varchar(21), Age int, Saving int, InsertDate DATETIME)

Steps

1. Prepare Data Flow Task like this

2. Add Derived Columns with Expression. We can use system variables, columns from source, system functions, user variables.

3. Data conversion allows us to change the datatype, increase the length, etc..

4. do proper mapping in destination