Derived Column — SSIS

When processing table or view data, sometimes we can’t directly use the data we need. In such cases, SQL Server Integration Services (SSIS) users employ the Derived Column transformation to manipulate data and create new columns.

The Derived Column transformation is a type of transformation in SSIS data flows. This transformation creates a new column by applying a specific operation to the data in the input column. The result of this operation is added to the data flow as a new column, making it possible to generate new data based on existing data.

For example, let’s say we want to calculate the age based on the birth dates of customers in a table. In this case, we can create a new column using the Derived Column transformation. This new column will contain the calculated age information based on customers’ birth dates.

Additionally, using this transformation, we can perform various operations such as mathematical operations, logical expressions, string operations, and date operations. For instance, we can use the Derived Column transformation to calculate the total order value based on the quantity and price of orders in a table.

In summary, the Derived Column transformation is a highly useful tool for processing data and creating new columns in SSIS data flows. This transformation supports many operations and helps make data more meaningful.

After defining our source, we drag and drop the Derived Column task onto the screen and connect the source task to the Derived Column task.

When we right-click on the Derived Column task to edit it, parameters, columns in the table, and various functions we want to perform on the right side of the screen are displayed.

For example, if we want to find the total discount amount, we need to multiply the columns OrderQty and UnitPriceDiscount in the table. To perform the relevant operation, we need to specify the column name and use functions in the expression field.

Upon executing our package, we observe that it runs smoothly.