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.

We can create the required expression using those built-in functions, Columns, Operators and variables. SSIS Derived Column Transformation 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

STEP 1: 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 data flow tab.

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

STEP 3: 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

STEP 4: Click on 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. This will allow the transformation to use the source data.

SSIS Derived Column Transformation Expression

STEP 5: 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 2 options. Whether you want to add this as a new column or you want to replace any existing column with this one
3. Expression: In this place, we will write 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 then 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. Drag and drop the blue arrow to the ADO.NET Destination

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

STEP 7: Click on the Mappings tab to check whether the source columns are 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 SSIS derived column transformation

Thank you for Visiting Our Blog