Derived Column Transformation in SSIS plays a vital role in dealing with expressions in SQL Server Integration Services. The 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 ones with values calculated from expressions.
Derived Column Transformation in SSIS Example
Drag and drop the data flow task from the SSIS toolbox to control flow and rename it as Derived Column Transformation.
Double click on it, and it will open the 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 the OLE DB source in the data flow region will open the connection manager settings and provides space to write our statement.
The 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 fields. In this tab, we can uncheck the unwanted columns also.
TIP: If we don’t want any column, then there is no point in adding it to 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.
Expression
Double click or right-click on the derived column transformation to edit
- Derived Column Name: Provide any unique name. Same as the Alias column in T-SQL.
- Derived Column: This derived column provides two options. Whether you want to add this as a new one or you wish to replace any existing column with this one
- Expression: In this place, we will write a custom expression by combining the built-in function, variables, and Columns.
- Precision: When we add any new field, Derived Column Transformation in SSIS automatically sets the precision for numeric data based on the data type. The value of this is read-only.
- Scale: When we add any new field, then Derived Column Transformation automatically sets the scale for numeric data based on the data type. The value of this is read-only.
- Code Page: When we add any new column, it automatically sets the code page for the DT_STR data type.
- Configure error output: Specify how to handle errors.
- Upper First Name: UPPER (FirstName) – It will convert the data present in the [FirstName] column to Upper Case.
- Full Name: FirstName + “ ” + LastName – It will combine the [FirstName] and [LastName] columns to display the Full Name
- Tax: SQRT ([YearlyIncome]) – It will calculate the Square Root of the [YearlyIncome] column and assign it to Tax Column
- Variable Pay: 5000 – We are assigning the constant value 5000 to the new column Variable pay
- YearlyIncome: REPLACENULL ([YearlyIncome], 0) – We are replacing the Null values with Zeros using the REPLACENULL built-in function.
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 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 Management Studio and Check the results of the derived column transformation.
