SSIS Derived Column Transformation

SSIS Derived Column Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

Data usually must be transferred after replacing the values in some columns, or a new, calculated column is needed. This is when the Derived Column can be used.

The Derived Column transformation can change multiple column values at the same time. It supports error outputs since it applies updates on the data. The Derived Column can have only one input and an output (excluding the error output, of course).

When a new column is added, or an existing one is replaced, you have to define the (new) value with the syntax of SSIS Expressions:

In the example above, 3 new columns are added to the data flow: LengthOfLabel, Year, And LoadTime. The expressions defined to them were created by drag&drop operations. The variables and columns can be dragged onto the Expression column from the upper left pane, the functions can be added from the righ upper pane.
The values of the ID column are incremented by 1. No new column created in this case, the change is applied in-place.

The Derived Column is a synchronous transformation  (also known as row transformation), which means that each of the output rows are in a 1:1 relation with an input row, and the output of the transformation is reusing buffers and no new thread is introduced into the data flow.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Zoltán Horváth edited Original. Comment: minor formatting: text alignment (Full)

Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Zoltán Horváth edited Original. Comment: minor formatting: text alignment (Full)

  • Zoltán Horváth edited Revision 1. Comment: changed link to SSIS Expression

Page 1 of 1 (2 items)