Derived Column Transformation

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.

You can use this transformation to perform the following tasks:

• Concatenate data from different columns into a derived column. For example, you can combine values from the FirstName and LastName columns into a single derived column named FullName, by using the expression FirstName + " " + LastName.
• Extract characters from string data by using functions such as SUBSTRING, and then store the result in a derived column. For example, you can extract a person's initial from the FirstName column, by using the expression SUBSTRING(FirstName,1,1).
• Apply mathematical functions to numeric data and store the result in a derived column. For example, you can change the length and precision of a numeric column, SalesTax, to a number with two decimal places, by using the expression ROUND(SalesTax, 2).
• Create expressions that compare input columns and variables. For example, you can compare the variable Version against the data in the column ProductVersion, and depending on the comparison result, use the value of either Version or ProductVersion, by using the expression ProductVersion == @Version? ProductVersion : @Version.
• Extract parts of a datetime value. For example, you can use the GETDATE and DATEPART functions to extract the current year, by using the expression DATEPART("year",GETDATE()).

You can configure the Derived column transformation in the following ways:

• Provide an expression for each input column or new column that will be changed. For more information, see Integration Services Expression Reference.

  Note

  If an expression references an input column that is overwritten by the Derived Column transformation, the expression uses the original value of the column, not the derived value.

• If adding results to new columns, change the data type. For more information, see Integration Services Data Types.

  Note

  If you are deriving data to a date or a date time data type, the date is in the ISO format, although the locale preference may specify a different format.

• If adding results to new columns, set the column length of string data and the precision and scale of numeric data. For more information, see Precision, Scale, and Length (Transact-SQL).

• If adding results to new columns and the data type is string, specify a code page. For more information, see Comparing String Data.

The Derived Column transformation includes the FriendlyExpression custom property. This property can be updated by a property expression when the package is loaded. For more information, see Using Property Expressions in Packages, and Transformation Custom Properties.

This transformation has one input, one regular output, and one error output.

Configuring the Derived Column Transformation

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the Derived Column Transformation Editor dialog box, see Derived Column Transformation Editor.

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

• Common Properties
• Transformation Custom Properties

For more information about how to set properties, click one of the following topics:

• How to: Derive Column Values Using the Derived Column Transformation
• How to: Set the Properties of a Data Flow Component Using a Component Editor
• How to: Set the Properties of a Data Flow Component in the Properties Window
• How to: Set the Properties of a Data Flow Component Using the Advanced Editor

See Also

Concepts

Creating Package Data Flow
Integration Services Transformations

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Updated content: Clarified that column configuration is available only if results are added to new columns.
5 December 2005	New content: Added information about using property expressions with the FriendlyExpression custom property.