Thursday, February 2, 2012

SSIS-Derived Column

The Data Flow task within SSIS has several task components, some of the components can be very useful while performing ETL operations. One of the common task that is done would be is audit the source data that has been loaded in terms of the number of rows at source and the number of rows that actually got loaded in destination. The Derived Column task in the Data Flow is one of the very useful components which enabled one to add more meaningful information to the destination database on top of what is already being imported from the Source. The power of the Derived columns lies in that fact it can work on columns which are being provided as inputs to this task, the task can also work on System and User defined variables and columns can be generated based on expressions. Here  I would like to show by an example as to how a column can be generated based on a expression. For example if one needs to have a Status column generated which in turn will be mapped to a column on a Sql Server table, look at the snapshot below.

In the figure above a Column called Status is generated based on the Expression "Success". In the following example Derived columns are also generated based on user defined and system variables:

From the above snapshot several columns have been derived from variables, all of these can be mapped to a table say in sql server database and the data can be loaded.  When the derived column is connected to a OLE DB Destination task, all the derived columns would be available for mapping.

1 comment:

  1. Hey! Someone in my Facebook group shared this website with us so I came to
    check it out. I'm definitely enjoying the information. I'm bookmarking and
    will be tweeting this to my followers! Outstanding blog and superb style and design.
    Feel free to surf my website - weightloss calculator