Lookups and their error flows

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

Phil Brammer wrote:

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

Phil,

What do you mean with 'Can't do it'. Are you actually receiving an error?

I was able to set that up; two error outputs from lookup transformations going to an Union All; it seems to work; but that was with a simple test scenario I made. Yes, ErrorColumn and ErrorCode were there as well but is up to you to use them or remove them from the pipeline. What is actually no there is the lookup value but I would not expect to be since the lookup fail.

Another way to accomplish something like this is to configure lookups to 'ignore errors' and then at the end to use a conditional split to send the rows having nulls on the lookup values column to the error branch of the dataflow. Just a thought

Rafael Salas

|||User error... You made me think about it again, and when I looked at what I was trying to do, I realized my mistake. Never-the-less, what I was doing was taking the error "redirect" from the lookup and adding a new column with a derived column transformation. This was repeated on the other lookup error flows. Then my mistake (an accident!) was taking the *error* output of the derived column transformations instead of the data flow output and pushing them to the union all transformation.|||

oh, I see!. BTW, in my previous post I meant ignore (instead redirect) error as an alternative way. I corrected it though.

Rafael salas

sql

Lookup table

I have a situation where I need to use the following logic. If an item is non-stocking at LOC1, the system checks LOC4 first to see if it is stocking at that location, LOC2 second, LOC3 third, etc. If no stocking LOC is found, the records remain unmodified.

Stocking LOC (in order of precedence)
Non-Stocking LOC 1 2 3 4 5

LOC1 LOC4 LOC2 LOC3 LOC5
LOC2 LOC4 LOC1 LOC2 LOC5
LOC3 LOC5 LOC4 LOC1 LOC2
LOC4 LOC1 LOC3 LOC2 LOC5
LOC5 LOC3 LOC4 LOC1 LOC2
LOC6 LOC4 LOC2 LOC1 LOC5 LOC3
Right now we have a whole series of IF statements to work through this logic. When we add a location, a lot of code needs to be changed. Is there a way to build this into a table and be able to do the lookup more effectively, and that would make it easier to add a location?

Thanks in advance.I'd propose a more normalized table with 3 fields:
Non_stocking_loc, stocking_loc, precedence

Given a non-stocking loc, it's easy to retrieve all stocking locs for it, ordered by precedence.