Step 4: Adding and Configuring the Derived Column Transformation
In this task, you will add a Derived Column transformation to your package. A Derived Column transformation is a data flow component that creates new data values by using values in a dataset, constants, and variables, or by applying functions. You will use this transformation to add a new column and then populate the column with the evaluation results of an expression.
The user interface for the Derived Column transformation includes the expression builder. This graphical tool makes it easy to quickly write complex expressions using drag and drop operations, and provides templates for functions, type casts, and operators as well as the input columns and variables.
In the Derived Column transformation, you will create an expression that concatenates the values in the FirstName, MiddleInitial, and LastName columns in the dataset and then writes the result to a new column. Because the middle initial may be null, the expression will include special handling of this column. The new column, FullName, will be added to the transformation output.
To add a Derived Column transformation
If not already open, open the Data Flow designer, either by double-clicking Data Flow Task or by clicking the Data Flow tab.
In the Toolbox, expand Data FlowTransformations, and then drag a Derived Column transformation onto the design surface of the Data Flow tab, below Sort by State and City.
On the Data Flow design surface, click Derived Column in the Derived Column transformation, and change the name to Add FullName Column.
Click Sort by State and City and drag its green arrow to Add FullName Column.
Double-click Add FullName Column to open the Derived Column Transformation Editor dialog box.
In the left pane, expand the Columns folder, click the FirstName column and drag it to the Expression box.
In the Expression box, after [FirstName], type + " " +.
In the Columns folder, click the MiddileInitial column and drag it to the Expression box.
Update [MiddleInitial] to (ISNULL(MiddleInitial) ? "" : MiddleInitial + " ") + .
In the Columns folder, click the LastName column and drag it to the Expression box.
Verify that the value in the Expression box is the following:
FirstName + " " + (ISNULL([MiddleInitial]) ? "" : [MiddleInitial] + " ") + [LastName]
You may optionally remove the brackets that enclose column names in the expression. The column names are regular identifiers, which do not need to be enclosed in brackets. Names that contain invalid characters, such as spaces, must be enclosed in brackets. If the expression has been typed incorrectly, the expression text will appear in red.
In the Derived Column box for the row you just created, select <add as new column>.
In the Derived Column Name box for the same row, type FullName.
If the Data Type box is not already set to Unicode string [DT_WSTR], select Unicode string [DT_WSTR] in the Data Type list.
Set the value of the Length box to 103 (the sum of the lengths of the FirstName, MiddleInitial, LastName columns, and two spaces.)
Click OK.
In the Properties window, verify that the LocaleID property is set to English (United States).
See Also