Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 17. Creating and Replacing Columns with the Derived Column Transform

The Derived Column Transform allows you to either create or replace a column in the data stream. This component can be used for many problems you may run into and, therefore, is one of the most useful tools you have in the Data Flow. As you are going to see in this lesson's "Try It" example, the transform can be used for things like adding row auditing and editing incoming data using the available SQL Server Integration Services (SSIS) expressions.

You open the Derived Column Transformation Editor as you open other transform editors, by dragging it into the Data Flow and then double-clicking. To configure this transform, drag the column or variable into the Expression column, as shown in Figure 17-1. Then add any functions to it. You can find a list of functions to use as a reference in the top-right corner of the Derived Column Transformation Editor; the functions can be dragged into the Expression property. You must then specify, in the Derived Column drop-down box, whether you want the output of the expression to replace an existing column or create a new column. Lastly, if you create a new column give it a name in the Derived Column Name column.

In Figure 17-1, the expression states that if the column PhysicalCount is null, then convert it to 0; otherwise, keep the existing data.

To get the most bang for your buck with this transform, explore the different functions available. The functions and the availability ...