I need to do a partial fuzzy lookup in SSIS. This means that some columns
need to match exactly, while others are fuzzy matched. Picture an incoming
data stream T1 with two columns T1_C1 and T1_C2, and a lookup table T2 with
columns T2_C1 and T2_C2. Stream T1 has 16,000 rows, and lookup T2 has
215,000 rows. I want to tell the system to fuzzy match T1_C2 to T2_C2 with a
threshold of 80%, but only where T1_C1 and T2_C1 match exactly (100%).

That's the basic problem. To complicate this slightly, T1_C1 and T2_C1
actually will be matched using a SQL "LIKE" operator. T1_C1 contains values,
and T2_C1 contains a pattern which we store. FYI, I'm trying this in SQL
2008, though I don't believe there's been any change in fuzzy capabilities.

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

Hi dtbascent,

The best method would be to create a view (V1) on top of T2, which
decomposed the T2_C1 "pattern" into a column that will exactly match
T1_C1. Then you can simply do a fuzzy lookup with exact match on
T1_C1 = V1_C1

If this is not possible I think you'll probably have to split this
into two tasks - 1 regular lookup and 1 fuzzy lookup. The bad news is
the only way to do "LIKE" checks (afaik) is to turn on memory
restrictions in the regular lookup task, and write your own SQL
statement. This will mean that your lookup matches are done row-at-a-
time instead of buffered and done in memory. The number of rows
you're working with isn't too high though, so you might be alright.

Once you've retrieved the T2_C1 match rows, you can feed them into
your fuzzy lookup and do an exact match on that column.

Good luck!
J

I discovered that the basic functionality I need is supported in the fuzzy
lookup task. You can specify requiring an exact match on some columns, and,
at the same time, a fuzzy match on others. To handle the "Like", I
essentially did create a view that did the like matching first and tied the
matching key identifiers in both tables. Then in the fuzzy look up I exact
matched on the keys and fuzzy matched on the remaining columns.