Derived Column Transformation in SSIS

Derived Column Transformation in SSIS plays a vital role in dealing with expressions in SQL Server Integration Services. The SSIS Expression Language has powerful built-in functions for string manipulation, data type conversions, mathematical functions, conditional expressions, and handling Null values.

Using SSIS derived column transformation, we can create the required expression using those built-in functions, Columns, Operators, and variables. The SSIS Derived Column Transformation also provides an option to choose whether you want to create new columns or replace existing columns with values calculated from expressions.

Derived Column Transformation in SSIS Example

Drag and drop the data flow task from the toolbox to control flow and rename it as Derived Column Transformation.

Double click on it, and it will open the SSIS data flow tab.

Drag and drop OLE DB Source, ADO.NET Destination, and Derived Column Transformation in SSIS toolbox to data flow region

Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.

SQL Command We used in the above screenshot is:

USE [AdventureWorksDW2014]
GO
SELECT FirstName,
       LastName,
       YearlyIncome
FROM DimCustomer

Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

TIP: If we don’t want any column then there is no point to add it into your SQL command.

Click ok and drag the blue arrow from OLE DB Source to Derived Column Transformation in SSIS. It allows the transformation to use the source data.

SSIS Derived Column Transformation Expression

Double click or right-click on the ssis derived column transformation to edit

1. Derived Column Name: Provide any unique name. Same like Alias column in T-SQL
2. Derived Column: This SSIS derived column provides two options. Whether you want to add this as a new column or you wish to replace any existing column with this one
3. Expression: In this place, we will write a custom expression by combining the built-in SSIS function, variables, and Columns.
4. Precision: When we are adding any new column, then Derived Column Transformation in SSIS automatically sets the precision for numeric data based on the data type. The value of this is read-only.
5. Scale: When we are adding any new column, then Derived Column Transformation automatically sets the scale for numeric data based on the data type. The value of this is read-only.
6. Code Page: When we are adding any new column, Derived Column Transformation automatically sets the code page for the DT_STR data type.
7. Configure error output: Specify how to handle errors.

1. Upper First Name: UPPER (FirstName) – It will convert the data present in the [FirstName] column to Upper Case.
2. Full Name: FirstName + “ ” + LastName – It will combine the [FirstName] and [LastName] columns to display the Full Name
3. Tax: SQRT ([YearlyIncome]) – It will calculate the Square Root of [YearlyIncome] column and assign it to Tax Column
4. Variable Pay: 5000 – We are assigning constant value 5000 to new column Variable pay
5. YearlyIncome: REPLACENULL ([YearlyIncome], 0) – We are replacing the Null values with Zeros using the REPLACENULL built-in function in SSIS

Click ok to finish configuring the Derived Column Transformation in SSIS. Next, drag and drop the blue arrow to the ADO.NET Destination

Now, we have to provide the Server, database, and table details of the destination. So double-click on the ADO.NET Destination and provide the required information

Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns.

Click ok to finish the SSIS Derived Column Transformation package design. Let us run the package

Let us open the SQL Server Management Studio and Check the results of the derived column transformation