Validator Conditional Execution

Hi

Looking for some ideas

We have a validator in a transformation that has to check if a combination of values exists in a database table. I need a couple of things.

* If the record does not exist then I need to ABORT... But I'd like to set a package variable that can then influence workflow in the parent package.

* Not all rows in the source file need to be validated. If a certain value appears in a specific column (there could be a couple of columns) then the SQL Lookup doesn't need to occur, and the row can be pushed on to the next step in the Transformation. However, if the row has values NOT in this exclusion list then the SQL Lookup does need to occur and if the Lookup fails then the Transformation should ABORT.

* BUT because the transformation could FAIL for other reasons, we still need the parent package to know the difference between this specific SQL Lookup ABORT and an actual Failure or ABORT from another part of the transformation

I have a few ideas but just wanted to throw it out there to see what ideas may exist.

A diagram of what i am talking about is attached

cheers

 

We will have a look at this one for you

1  If the record does not exist then I need to ABORT... But I'd like to set a package variable that can then influence workflow in the parent package.

Right now the only option is to use "MagicToken"

Here is how:

 

 

 

begin
 if [F001]='1' then
   begin
    SetVariable('<VariableName>','Value');
    raise;
   end;
 Result:=[F001];
end;
 

Another option is to extend the functionality of the software.

 

* Not all rows in the source file need to be validated. If a certain value appears in a specific column (there could be a couple of columns) then the SQL Lookup doesn't need to occur, and the row can be pushed on to the next step in the Transformation. However, if the row has values NOT in this exclusion list then the SQL Lookup does need to occur and if the Lookup fails then the Transformation should ABORT.

>>> Use a validator to redirect the records which do require SQL Lookup execution

Just letting you know that in the latest release of Advanced ETL Processor, we have added "Set Package Variable" option to the lookup function



We are planning to do the same for the rest of the validation function but it is a lot of work so it will take a while