none
Fuzzy lookup-Correct Speeling and load

    Question

  • 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

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

    Tuesday, October 22, 2013 8:30 PM
    Moderator
  • 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

    Tuesday, October 22, 2013 8:40 PM
    Moderator
  • 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

    Tuesday, October 22, 2013 10:44 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

    Tuesday, October 22, 2013 8:30 PM
    Moderator
  • 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

    Tuesday, October 22, 2013 8:40 PM
    Moderator
  • 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

    Tuesday, October 22, 2013 10:44 PM