SSIS: Derived Column Transformation

Transforming data into new fields.

This example will demonstrate the three primary components of a data flow: data source, data pipeline, and data destination.

First, drag an OLE DB source unto the designer.


Configure the data source by configuring a new connection manager and the correct database and table.
For this example, I will only leave three columns selected for retrieval.

Now drag/drop a Derived Column transformation and connect the green line between the data source and transformation.


Configure the transformation to use the String UPPER() function to aggregate the First and Last name values into a new derived column.

Drag a DataReader destination unto the designer and connect the Derived Column’s green line to it.


Right-click the destination to configure its input using the Advanced Editor.

Add a Dataviewer between the data source and the derived column by right-clicking the pipeline between them, select Dataviewer.
Add a Grid viewer and configure the columns for display.

Now use the same steps to add a Dataviewer between the Derived Column and the destination using the same steps.

Execute the package and notice the it stops with both the source and destination are yellow indicating execution has paused.
This is due to the first data viewer has been populated and awaiting response from the user.

Choosing to “Detach” the first viewer allows the second viewer to be populated and the source’s color is now green.
By detaching again, the package will complete execution.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s