Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 21

Creating and Replacing Columns with the Derived Column Transform

The Derived Column Transform enables you to either create or replace a column in the data stream. You can use this component for many problems you may run into and, therefore, it is one of the most useful tools you have in the Data Flow. As you see in this lesson’s Try It example, you can use the transform for things like auditing rows and editing incoming data using the available SQL Server Integration Services (SSIS) expressions.

You open the Derived Column Transformation Editor 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 21-1. Then you can add 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; you can drag the functions 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 to create a new column. If you create a new column, give it a name in the Derived Column Name column.

In Figure 21-1, the expression states that if the column PhysicalCount is NULL, 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 of variables makes ...