Remove From My Forums

Fuzzy lookup-Correct Speeling and load

Question

0

Sign in to vote

Hi all

I am having Department table(Master) to list out all Department name, i am loading data from text file to Destination table,if any spelling mismatch between Master and Department column in text file, how to correct the spelling and load in to destination, please help me

i am using Fuzzy lookup, after Fuzzy lookup how to go ahead.please guide me.

i am new to SSIS.

Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

Tuesday, October 22, 2013 6:45 PM

Reply

|

Quote

Answers

0

Sign in to vote
Is the spelling issue a result of user input?

There are simply no 100% solutions to deal with spelling mistakes, but a specific word can be approximately matched to a base word using say SOUNDEX you can use to drive corrections.

So in short I envision you accumulate a list of mismatches then run each against soundex and then compare to an expected value and if it is close enough make a correction.
Arthur My Blog
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 8:30 PM

Reply

|

Quote

Moderator

0

Sign in to vote
You can use the Fuzzy Lookup to check the value from your source against the master table. The Fuzzy Lookup will give two numbers Similarity and Confidence. If they both 1 (100%) then the spelling is correct.

If it's less then 1 the spelling is not correct, but if the score is too low then you can't use the lookup value to correct it.

You can use a Derived Column to do the checks. Something like:
[_Similarity] == 1 && [_Confidence] == 1 ? [oldvalue] :
[_Similarity] >= 0.8 && [_Confidence] >= 0.8 ? [oldvalue] : [correctvalue] : ""
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 8:40 PM

Reply

|

Quote

Moderator

0

Sign in to vote
This blog post may be useful

http://sqlserverrider.wordpress.com/2011/10/18/fuzzy-lookup-approximate-to-exact-search-ssis/

Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 10:44 PM

Reply

|

Quote

All replies

0

Sign in to vote
Is the spelling issue a result of user input?

There are simply no 100% solutions to deal with spelling mistakes, but a specific word can be approximately matched to a base word using say SOUNDEX you can use to drive corrections.

So in short I envision you accumulate a list of mismatches then run each against soundex and then compare to an expected value and if it is close enough make a correction.
Arthur My Blog
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 8:30 PM

Reply

|

Quote

Moderator

0

Sign in to vote
You can use the Fuzzy Lookup to check the value from your source against the master table. The Fuzzy Lookup will give two numbers Similarity and Confidence. If they both 1 (100%) then the spelling is correct.

If it's less then 1 the spelling is not correct, but if the score is too low then you can't use the lookup value to correct it.

You can use a Derived Column to do the checks. Something like:
[_Similarity] == 1 && [_Confidence] == 1 ? [oldvalue] :
[_Similarity] >= 0.8 && [_Confidence] >= 0.8 ? [oldvalue] : [correctvalue] : ""
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 8:40 PM

Reply

|

Quote

Moderator

0

Sign in to vote
This blog post may be useful

http://sqlserverrider.wordpress.com/2011/10/18/fuzzy-lookup-approximate-to-exact-search-ssis/

Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Friday, October 25, 2013 4:38 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Wednesday, October 30, 2013 4:07 PM
Tuesday, October 22, 2013 10:44 PM

Reply

|

Quote