0

Aim

I work with the KingswaySoft SSIS Add-In tool "Premium Lookup", but I guess that the same question might arise with the built-in SSIS "Lookup" tool.

I try to match on a double column pair "first_name1" and "name1" and "first_name2" and "name2" and both side 1 and side 2 sometimes have NULL values.

If one of the lookup columns is NULL, the whole row is passed to the "unmatched" output. The Premium Lookup tool looks as follows in a DFT: The input is passed to 32 unmatched rows since one of the two sides has a NULL value or since there is a NULL value in some other matching column:

enter image description here

My aim is to keep matching the columns when a NULL in one of them is found, so that the lookup just goes on and tries matching the other columns as well, while the NULL-match should be:

  • either 0 % match
  • or NULL and not taken for the average score at all.

Both would be good since I have a few more columns that often match well enough.

I ask for a max of 5 matches and I drop any match from the output that has the same ID as the primary input since I want to find the double entries only. Therefore, up to 5 "next neighbours" shall be shown for any entry that might have duplicates in the master data:

enter image description here

For example, if the first name is not known in both of the sides and even in the primary input field, I still would want to get a match from the other columns like "birthday" or "sex".

What I have tried up to now

I have already tried replacing all of the NULL values with an empty string so that NULL is avoided. Yet, the even the fuzzy lookup must match at least by 1 percent, and 1 % cannot be reached if you try matching something against an empty string as that is 0 %.

Legend:

  • German / English: Vorname = first_name, Nachname = name, Geburtsdatum = birthday, Geschlecht = sex
  • Lookup: side 1 : [1], side 2: [2]
  • Primary side: [0] (I want to match the two lookup columns with the same primary column twice so that I copied the Nachname_Vergleich column to a second Nachname_Vergleich2 column2 and did the same with the Vorname column)

enter image description here

Checkbox "Treat Empty String as NULL" crashes the Premium Lookup at any input

I also tried working around that by setting those empty strings '' to NULL again by the tool itself, but that setting crashes the lookup for any input, be it NULL or not, tested during a few other tasks as well. Thus, there is a bug or I do not understand the setting, but I take my hands of that checkbox:

Advanced Comparison Settings - v22.1 (22.1.0.30606) -> "Treat Empty String as NULL"

enter image description here

Whenever you run it with a checked checkbox, it fails with three errors:

[Premium_Lookup_trying_to_match_NULLs [74]] Error: An error occurred with the following error message: "System.NullReferenceException: Object reference not set to an instance of an object. (SSIS Productivity Pack, v22.1.0.30606 - DtsDebugHost, v13.0.4495.10)".

[Premium_Lookup_trying_to_match_NULLs [74]] Error: The Premium_Lookup_trying_to_match_NULLs was unable to process the data.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Premium_Lookup_trying_to_match_NULLs" (74) failed with error code 0xC02090F9 while processing input "Lookup Table Input" (79). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

And even if it worked, it would not help since it would just replace the empty strings with a NULL, which would likely pass the rows to the unmatched output again how it was in the beginning so that nothing would be gained by replacing NULL:s with empty strings at first.

Workaround

Therefore, I do not get any matches from the "Premium Lookup" tool while the lookup works as soon as I set the matching of the two columns to "ignore".

My workaround is to replace side 1 with side 2 if side 1 is NULL, and then, it runs through as it should, but this also drops one chance to match if both sides are filled, and I want to get the two matching scores if the two columns are filled.

Therefore, the workaround is only the second best way since I want the lookup to just skip NULL:s or give them 0 scoring points instead since the missing scoring points average out over the found set of matches.

Question

For a lookup that has two sides against a primary input that has just one side, I want to match both sides of the lookup on their own against the primary input. I do not just want to replace lookup side 1 with lookup side 2 if there is a NULL. How can I do this and still skip NULL:s or give them 0 scoring points instead of losing the rows to the unmatched output, as it seems to happen here?

1

1 Answer 1

0

TLDR

The Premium Lookup tool can deal with NULL values.

Rebuild the whole data flow by deleting everything, also the Premium Lookup box, even if it seems to know what you changed in the meantime. In the fresh data flow, everything worked fine. Make sure that you have the strings that you try to match in the exact same format (case, whitespaces and so on, which you can either do by checking the checkbox in the Premium Lookup or in SQL).

The "quick" fix is to re-build everything and take care of the formats

There was a quick fix for this, here is the learning path.

I wondered why I could get matching scores if I matched on columns "birtday" or "sex", but not on "first name" or "name. It was since the default of a new matching pair in the "Premium Lookup Component Editor" is not always the same. If I match the birthday of char(8), it defaults to 75 % Speed and the marked first two checkboxes:

enter image description here

But when I added the name columns, the "Advanced Settings" defaulted to the second two marked checkboxes and 0 % speed:

enter image description here

When I made a fresh new lookup from scratch, the default was even nothing at all:

enter image description here

I had a default that did not ignore the case though that was needed.

It turned out that I had changed the columns to lower case in SQL only on the lookup side, but not on the primary input side, that is why the side 2 name columns could not match. And since I thought that changing both data flows to lower case was already done, I did not check it, and I oversaw it even when reading the bigger case letters quite often since I keep one column with the unchanged format as well.

Thinking that everything was already lower case, I only scrolled the "Similarity Threshold" to 1 %:

enter image description here

enter image description here

Thus, if you have not changed your text columns yourself to some default, you should check the two other boxes at the top. I set the speed to 75 % Best Speed as it is chosen in the other default since this fuzzy lookup is not about the 100 % stats.

enter image description here

Once done, this will lead to scores for all columns:

  • _similarity_side1.Geburtsdatum_INT_STR
  • _similarity_side1.Nachname_Vergleich
  • _similarity_side1.Vorname_Vergleich
  • _similarity_side2 Geburtsdatum_INT_STR
  • _similarity_side2.sex
  • _similarity_side2.Nachname_Vergleich
  • _similarity_side2.Vorname_Vergleich

And the needed stats over all, which are:

  • _average_similarity_score
  • _richness_score
  • _richness_rank
  • _confidence

Thus, the lookup tool works as it should, the average score can be seen for all even if some values are NULL.

enter image description here

Yet again, I ran into a lot of strange rows without any scores or too few scores, and I found samples where I knew from quick research that there must have been a match since there was no NULL value at all in it. I checked each box in the whole data flow, and after rebuilding just some of the boxes, there came just no scores at all for a setting that had some scores before the cleaning.

I decided to:

  1. clean the text already in SQL (this is not fully needed if you use the Premium Lookup options)
  2. re-build the whole data flow from scratch (THIS is needed).

Thus, 1.):

cast(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(A.Nachname), 'ü','ue'), 'ö', 'oe'), 'ä', 'ae'), 'ß', 'ss'), '-', ''), '`', ''''), ' ', ''), 'á', 'a'), 'é', 'e'), 'ë', 'e'), 'í', 'i'), 'ó', 'o'), 'ú', 'u'))) as varchar(100)) AS Nachname_Vergleich

I made sure that this is done for all of the six columns that need it (1x2 for the primary input and 2x2 for the lookup).

And 2.):

Have you heard of this TV series, something like "SSIS Crowd" or so? Something like: Have you tried building it off and on again?

Outcome

Data Flow

enter image description here

Thus, 160 for 32 rows means that for all of the data, the top 5 of the "_richness_rank" is shown.

What is not yet in the greenshot: you should sort the output first by your "ID" and second by the "_richness_rank" since the output is not always sorted, sometimes the rankings are out of place.

And you might want to drop any match that has just the same ID and therefore has the same entries, which in this data flow would drop 10 rows out of the 32 "ID"-rows:

enter image description here

Table output (stats columns)

We see that top 1 is sometimes dropped, which is just the 10 times that have the same master data ID in the lookup. I could also drop these rows before the lookup, but I think that it is good to know whether the ID is in the master data or not, which becomes clear just by scrolling. The top 5 ranks are a vague number anyway, and the first or second rank should be the only clear match.

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.