Exclusionary Look Up

[ShaneMuir]

Hi Everybody

I am wondering if its possible to use a lookup fileset to exclude records from a source?

My job runs as such:

Source: SQL Server Stage

Lookup Stage which references a lookup file set containing 3 columns all of which form the key. If a match is found on all 3 columns then I would like the input record to reject and if no match is found then the record to continue.

Does this make sense?

Thanks in advance for all your help.

[jhmckeever]

How about sending the matching records (on the output link) to a NULL destination. This could be either a Sequential File pointing to NUL, or a Copy stage with no output link. Unmatched records will appear on the Lookup's reject link which you would then use for downstream processing.

J.

[ShaneMuir]

Thanks for reply.

Actually at present I am using the reject link as the throughput, and the output link actually contains the "rejects" (ie the ones that found a match in the file set).

I was just wondering if there was a way of performing the task without having the throughput on the reject link.

[jhmckeever]

What's your reason for wanting to move away from having rejects go down the reject link? What have you got against the reject link? You're using the right solution for your requirement - don't feel bad that it's got a negative-sounding name

I think any alternative solution is likely to involve more stages, or more complexity, and (if that's the case) would most likely decrease performance and be harder to maintain.

Just my opinion,
John.

[ShaneMuir]

What's your reason for wanting to move away from having rejects go down the reject link? What have you got against the reject link? You're using the right solution for your requirement - don't feel bad that it's got a negative-sounding name

I think any alternative solution is likely to involve more stages, or more complexity, and (if that's the case) would most likely decrease performance and be harder to maintain.

Just my opinion,
John.

Thanks again John, its not that I want to move away from it, but you're right about the negative sounding name - to me it just seemed "wrong". So I thought I would ask if there was something obvious that I was missing. But you have alleviated my concerns. Thanks.

[ray.wurlod]

Yeah, it should be called the "Not Found" link. They got it right with Transformer, and it's "Otherwise/Log" link, but that's purely serendipitous; it's only because the Transformer also supports a reject link for handling rows that raise exceptions.