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

Lookups - multiple hits

An underlying assumption of the lookup task seems to be that there will be a unique record matching the lookup criteria. However what happens when more than one record is matched? Does it return the designated field of the first record encountered, or does it error out? Is there a way to specify what SHOULD happen in such a case? Is there a way to specify secondary criteria if the primary returns multiple hits?

Related to this - suppose that my lookup returns 2 data points, but one of them is null. It seems that the lookup should appear successful, and rows can then be redirected based on which field was null via the Truncation setting.
For example, say I'm matching on "name" and returning "Id" and "description". The name is found, and there's a valid Id, but the description is null.
Would this result in a successful lookup with the "description" truncated, or would the lookup fail?

Thanks!
PhilIn full cache mode the lookup will report a warning and use one of the records. In partial or no cache it will use one of the records with no warning. I say one of the records because this is an implementation detail and is subject to change.

You can always specify more than one join column so that you will not get multiple hits. Obviously, this is not exactly what you are asking for because it sounds like you only want the secondary match to occur if the primary is a dupe. This the lookup does not do.

NULL does not mean truncation. In the scenario you specify the lookup would be considered successful with a description that happens to be NULL. Trunctation is when you try to put a string that contains 100 characters into a column that is less than 100 characters.

Thanks,
Matt

Lookup value for a field?

Using VS2005 and creating reports. GUI has combo boxes with lookups. The
main table is storing the selected.value of the combo box, but the combo box
is displaying the "Name" column.
How do I display the "Name" column value on a report for a field instead of
the integer value of the row?
Thanks.If you look at the different options in the parameter creation window, it's
pretty straightforward.
1) While on the "Data" tab, go to "Report | Report Parameters" in the menu.
2) Click on "Add".
3) Give a meaningful name to your parameter (other than
"Report_Parameter_0")
4) Select a datatype (string, int, etc.). In your case, this would be int.
5) Type a prompt for your parameter (this will appear to the left of your
parameter combobox in the browser).
6) In the "Available Values" section, select "From Query"
7) Select the dataset you will use for your parameter values (typically a
separate dataset from the report's main dataset).
8) Put your key field (the integer value) in the "Value Field" drop down.
9) Preview, Rebuild or redeploy your report and voilà!
HTH,
Alain
"Brooke" <tbrooked@.hotmail.com> wrote in message
news:%23dSGgzfrHHA.5024@.TK2MSFTNGP04.phx.gbl...
> Using VS2005 and creating reports. GUI has combo boxes with lookups. The
> main table is storing the selected.value of the combo box, but the combo
> box is displaying the "Name" column.
> How do I display the "Name" column value on a report for a field instead
> of the integer value of the row?
> Thanks.
>