Using Derived Column Task in SQL Server Integration Services

In this article, we will learn how to create derived columns.

Derived column task in SSIS is used to,

• Create new column
• Update existing columns
• Merge different columns into one column. For example, businesses want to concatenate first name, middle name, and last name to make it a full name.

Now let’s understand this with an example.

We will use the AdventureWorksDW2019 database, which has already been loaded into the SQL server, to create a derived column function.

Now let’s go to SSMS and see the table how it looks right now.

So here let’s say, the business wants to merge the first name, middle name, and last name of the customers in one column, so to perform this operation we need to use the derived column task.

Now let’s go on the Data Flow Task in SSIS and perform the below steps.

Step 1. Create OLE DB Source connection – we will add OLE DB Source and Derived Column Task in the Data Flow Task and establish a source connection. We can see this in the screenshot below.

Step 2. Derived Column Transformation –To create new column values, we perform derived column transformation by applying expressions. We will go on Derived Column Editor to add derived column names and specify expressions to create new column values.

Step 3. Now click Ok and Create an OLE DB Destination connection – We will establish a new OLE DB Destination connection in the editor to push new data. So here will give the connection manager name and the new table name as CustomerNameDerivedCol.

Step 4. Now hit ok and go to Mappings to see available input columns and available destination columns, here you can notice that the same column name is mapped.

Step 5. Now the destination connection is established and the SSIS package is executed successfully.

Step 6. Now let’s verify this in SQL Server Management Studio (SSMS). In the below screenshot at the end of the result, we can see the CustomerName column. That’s how we derive a new column.

Summary

In this article, you have learned how to create the derived column in SSIS, hope you liked it. Looking forward to your comments and suggestions in the section below.