SSIS Derived Column Transformation

[[articles:SQL Server Integration Services (SSIS)|Integration Services]] uses transformations to manipulate data during an [[articles:Extract Transform Load (ETL)|ETL]] dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or [[articles:SSIS: List of Transformations|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 Expression]]s:

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 [[articles:SSIS: List of Transformations|this Wiki article]].