SSIS – Adding business logic layer in the data flow with less effort using Derived Column Transformation

There will be always change in everything. It is not a new story to IT department. Especially, data may change or need to change often to align with business. So, there is a new approach been followed in the software development. There are various layer in the framework of software design. Data layer, business logic, application, presentation and etc.

In ETL design, we may have to change the incoming original data with specific business logic. All data coming from a valid source which are compatible to the specific application. But in data warehouse scenario,  there will be lot of additional column to satisfy the business needs. So, we need to add additional column with business logic or replace new value with the existing column.

Derived Column

Derived column transformation creates or replace a column in the data flow. The purpose of the task is to do calculation with the SSIS package variables or input columns with plain expression or conditional expression.

It has functions and operators to perform calculation.

These functions and operators can be used in the expressions to derive new values. It simplifies most of the tough task of the SSIS developer.

Follow are the sample expressions that can be used in the Derived Column task.

• ABS( Any numeric value expression ) = Returns positive value only.
• RTRIM( string value expression ) = Removes empty space from the right side of the string.
• DATEADD( date value ) =  Also adds or subtracts  n number of days from the given date.
• ISNULL( «expression» ) = Returns true or false if the given expression output is null.
• (DT_STR, «length», «code_page») = Changes the data type of a column with given settings.
• «boolean_expression» ? «when_true» : «when_false» = Conditional expressions.

We can also use derived column transformation to implement the most complex expressions in the business layer.

Learn more about Derived Column Transformation.

Implementation

Step 1: Create a package and add a excel data source. Now, add an excel work book.

Step 2: Add Derived Column task and connect the data source task with it. Now, open Derived Column Transformation editor.

I have added a new column wages in the data flow. So, the data flow contains 3 columns. Name, Work hours and wages.

Step 3: Add union all task and connect derived column task with it. to view the result add data viewer control.

Step 4: Execute package now.

Thanks for reading.