Creating customized columns is among the most common requirements in the ETL. The Derived Column Transform provides a way to write custom expressions in order to create new columns or replace existing columns. The customization of generated columns is based on expressions.
In this recipe we will get a list of employees from the EmployeeDepartmentHistory table. We have two columns that serve as sources: StartDate and EndDate. We will fetch a value for the YearsInCompany field by subtracting the StartDate year from the EndDate year; and if EndDate is Null, then subtracting the StartDate from the current date.
Create a new empty file. Name it R01_Destination.csv in the C:\SSIS\Ch03_Data Flow Task-Part 2-Transformations\Files directory, then save it with utf-8 encoding. We will use this file as our destination. Select the package created by default and rename it to "P01_DerivedColumn.dtsx