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

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.
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.
Advertisements
  1. Margaret Norkett
    July 8, 2011 at 5:36 am

    Your sample expressions are very simplified and leave the impression that dateadd and rtrim perform only as described when in actuality dateadd can add whichever level of datetime is needed from milliseconds up to days, months or years. RTRIM is actually not a valid SSIS expression as SSIS and SSRS uses the VB TRIM instead. RTRIM in TSQL only removes the spaces on the right hand side of the string. VB’s TRIM removes spaces at both the left and the right hand sides.

    • July 8, 2011 at 8:56 am

      Hi Margaret,

      Thanks for your comment.

      I will alter the statement to make sure it is an example and not the details of the specified function. And also, I have tested RTRIM function with sample data in SSIS. It is working fine and removing space at the right side of the string. FYI, I am using SQL Server 2008 R2.

      Thanks
      Ayyappan

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: