How to utilize Fuzzy Matching Capabilities of SQL 2005 to match records from various enterprise directories, when no mutually unique identifiers exist among the directories.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

 Quite often I find myself in a situation where I need to establish a link between records in disparate directories and no mutually unique identifier exists within the directories. A common example of such situation is synchronization of HR and Active Directory. Within HR system employee records are identified by employeeID and in Active Directory accounts are uniquely identified by objectSID attribute, but employeeID field in Active Directory is not populated with the corresponding employeeID.

In the absence of a unique identifier we typically resort to matching records by a combination of fields (Last Name, First Name, Initial etc), but in a large enterprise with tens of thousands of records this approach does not work well due to a variety of reasons:

• Spelling errors
• Duplicate records
• Large number of identities with the same combination of Last and First names

Luckily with the release of SQL 2005 we nwt have another tool in our arsenal that would allow us to streamline the process of matching records in disparate directories by utilizing Fuzzy Lookup capabilities of BI engine that comes with this product.

Please, follow this link for a step-by-step walkthrough.