Answered by:
Fuzzy lookup-Correct Speeling and load

-
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.
Question
Answers
-
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
-
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
-
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
All replies
-
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
-
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
-
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