SSIS Error Logging (Data Flow Compoment Derived Column)

Question

Hello,

What the best way to check the column (in DerivedColum component) is NULL. If NULL => log error else continue with data flow.

Regards

Answer 1

This might help you partially.

Flowing the derived column control into a condional split control. To flow only non-nulls create a condition

!(ISNULL(COLUMN_NAME))

Then connect the conditional split back to your standard flow. This will flow the correct instances as your require.

The thing I can't quite figure out is the logging of this error. Maybe try creating a second condition:

(ISNULL(COLUMN_NAME))

And flow that into some dataflow task that will cause an error to be raised, not really elegant though. Or you could try flowing into another derived column that uses values in the row to form an output to some log file.

Answer 2

Place conditional split transformation before or after derived column transformation to remove rows with NULLs.

OR:

1. Create a Dummy variable with value -1.
2. In the derived column use something like ISNULL(myCol) ? (DT_I4)SQRT(@[User::Dummy]) : myCol
3. Configure error output to redirect row on error.
4. Connect error output to a flat-file destination.

Note:

• SQRT(-1) in the step 2 raises error.
• Make sure that type-cast in step 2 matches myCol type; I have used DT_I4.