SSIS Derived Column Transformation

The Derived Column transformation provides a way to create new columns by using expressions on the input columns. Like Expression Task, The expressions can be created in the Derived Column Transformation Editor which is similar to the Expression Builder. The new columns will be added to the output columns of the transformation.

In the following example, we'll create a new package from the one we created in Error Output section and add 2 derived columns in the Derived Column transformation to handle NULL value situations. The source Excel file Book.xlsx is kept unchanged. Let's get started to build the new package.

  1. Copy & paste the package ErrorOutput.dtsx in the project LearnSSIS2 to create a new one and rename it to DerivedColumn.dtsx.

  2. Click Data Flow tab and drag & drop the Derived Column from SSIS Toolbox to the data flow and link them as below.
  3. [Add Derived Column Transform]

  4. Right-click "Derived Column" transform and choose "Edit..." to open the Derived Column Transformation Editor. Then add 2 derived columns "Copy of Price" and "Copy of Brought Date" and drag & drop Conditional operator to the Expressions of the 2 columns.
  5. [Derived Column Editor]

    Apparently, the expressions are not finished so they are displayed in red. Keey the default setting "" in Derived Column because we don't want to replace any columns. If you want to configure the error output of this transform, you can click the button "Configure Error Output...".

  6. Finish the expressions.
  7. [Two Expressions]

    The price is set to 0 if it is NULL and the bought date is set to the current date if it is NULL.

  8. Click OK button.
  9. You can see there is no error icons. Is it done? Actually not.

    The new derived columns have not been added in the data pipeline and the old mapping is still defined in the destination transform.

  10. Right-click the "OLE DB Destination" to choose "Edit..." to open its editor, then click Mappings tab and change the Input Column as shown below.
  11. [Change Input Columns]

  12. Click OK button, remove all the records in Books table in Test database and run the package.
  13. [Derived Column Package Running Result]

    There is no error column any more. Let's check the database table.

    [Result In Books Table]

    The 2 records were added as expected.