Home > SSIS Best Practices > SSIS – Derived Column Transformation

SSIS – Derived Column Transformation

Derived Column transformation offers considerable flexibility and versatility combined with extreme ease of use. It can generate output data rows through custom-built expressions (constructed with a wide range of built-in functions and operators), which, besides referencing input columns might also leverage values stored in package variables.

Here are some basics of using the Derived Column transformation. In the Derived Column transformation editor, we can hard code a value in teh expression for the derived column, for example a space ” “, or we can drag the Null function for the DT_WSTR data type into the Expression textbox, and then enter NULL(DT_WSTR, 50) into the expression textbox. We can also use a ternary syntax:

[Test Condition] ? [True Operation] : [False Operation]

Example: IsNull(CustName) ? ” ” : CustName

In the Derived Column dropdown, we can also select “<add as new column>” to add a new derived column.

The Derived Column Transformation seems to be very flexible and powerful. The mathematical and string parsing functions are easily understood. Other ETL-specific functions, such as NULL fields, type-casting, and ternary conditional operations, are not as commonly understood.

Categories: SSIS Best Practices
  1. No comments yet.
  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: