DSXchange

Hello,

My first post...be gentle please.

I was curious on input regarding a logging solution. The goal is to evaluate each required column for each record and capturing all exceptions to a logging table. For each exception a new row would be written. In this case a record with exceptions in multiple columns would generate multiple rows in the table.

As a simple example:
Input table contains columns: name, state, zip, phone. In a transformer stage we evaluate state for 2 characters, zip for 5, and phone for 10.

Exception table has columns to capture: column_name, error_code, exception_value.

A record with 2 invalid columns would generate 2 records in the exception table.

Any thoughts on the best approach to handle? Currently we are creating a separate link for each column to a funner stage which then combines the exceptions. This can become tedious when faced with a very wide table.

I was thinking some form of loop mechanism but want to solicit feedback. Any input is appreciated.

I've built something similar using a single output exception record with dedicated fields for each possible field exception. Last step before loading them to the target table is a horizontal pivot of columns to rows with a filter to remove any 'empty' records.