Common mistakes or errors while working with Derived Column transformation component

1) Many a times columns are compared with some value to determine a column value. But if the column contains NULL values, this component would break as it would not be able to make a comparison. So always make a check on the column that you are using for comparison that the value is not NULL using the ISNULL function and then place the formula using the logical AND operator. This would make your formula bulletproof.

2) When you change an existing formula, the data-type is automatically changed by this component to Unicode until and unless it is specifically casted to ANSI. And this sudden change can break components ahead in the flow like Sort for example, which detects any metadata changes. A better solution can be to edit the formula in the Advanaced Editor to get rid of this buggy behaviour of this transform. This specially is the case in SSIS 2008.

3) While developing a formula, many get confused with the return types. Make sure that across the formula, return type should be of the same data type irrespective of the condition in the formula. Even if the intention is to return NULL, just cast the NULL to specific data type that the other part of the logic is returning.