CODEWITHSUNDEEP
ssis
Sreedhar
Sreedhar

Reputation: 30025

SSIS Error Logging (Data Flow Compoment Derived Column)

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

Regards

Upvotes: 0

Views: 1228

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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.

Upvotes: 1

Mutation Person
Mutation Person

Reputation: 30498

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.

Upvotes: 1

Related Questions