I need to capture certain data in error table which has the following feilds:
- SrcFileName
- ColumnName
- ColumnValue
The basic rules for identifying a bad value is like - checking if the column is not a valid number or if its not a valid date. For eg., the Src looks like this:
Ok, as you're going to perform validation within a transformer, create one or more output links specifically for the error table records and build those records based on the validations that fail.
- james wiles
All generalizations are false, including this one - Mark Twain.
EMP_ID B_DT Age Salary Join_DT
1000 1972-01-01 39 500B 19AP-01-01
For the above example you could use 2 stage variables :
stgvar1 : This would be like
If not(isvalid("int32",EMP_ID)) then 1
Else if not(isvalid("DATE",B_DT)) then 2
Else If not(isvalid("int32",AGE)) then 3
Else If not(isvalid("int64",SALARY)) then 4
Else If not(isvalid("DATE",JOIN_DT)) then 5
Else 0
stgvar2 : I would be
If stgvar1=1 then 'EMP_ID'
Else If stgvar1=2 then 'EMP_ID'
Else If stgvar1=3 then 'AGE'
Else If stgvar1=4 then 'SALARY'
Else If stgvar1=5 then 'JOIN_DT'
Else ''
you can have the link contraint to feed your error table using
You should end up with multiple stage variables, with at least one for each column which acts as a True/False indicator for whether or not a column failed validation. Because you wrote the code, you should know which columns are failing and can hardcode the name.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.