Derived Column Transformation Tasks - SSIS Data Flow
The Derived Column transformation in SSIS Data Flow allows you to create a new column or modify an existing column based on an expression.
Syntax
DerivedColumnColumnName = Expression
Example
Suppose you have a table with columns FirstName and LastName, and you want to create a new column called FullName that concatenates the two columns with a space in between. The expression would be:
[FULLNAME] = [FIRSTNAME] + " " + [LASTNAME]
Output
| FirstName | LastName | FullName |
|---|---|---|
| John | Doe | John Doe |
| Jane | Smith | Jane Smith |
| Bob | Johnson | Bob Johnson |
Explanation
The expression in the example creates a new column called FullName, which contains the concatenated values of the FirstName and LastName columns. The plus sign (+) concatenates the strings with a space in between.
Use
The Derived Column transformation can be used to create new columns, modify existing columns, or perform calculations on existing columns. It is often used in data transformation processes, such as ETL (extract, transform, load) tasks.
Important Points
- Expressions can include built-in SSIS functions and operators, as well as user-defined variables and functions.
- The expression editor in the Derived Column transformation provides a syntax checker and intellisense to help you write correct expressions.
- The Derived Column transformation can change the data type of a column. For example, you can convert a string to an integer using the
DT_I4cast operator.
Summary
The Derived Column transformation in SSIS Data Flow is a powerful tool for creating new columns, modifying existing columns, and performing calculations on data. With its flexible syntax and built-in functions, it allows you to easily transform data to meet your specific needs.