SQL Server Integration Services uses the fuzzy lookup data flow transformation component to find near matches between a data source and a reference table. The data source can be a SQL Server table, in which case you can define a SELECT query with a WHERE clause that only returns those records you need. But the reference table does not provide this sort of filtering.
Showing posts with label SQL Server Integration Services. Show all posts
Showing posts with label SQL Server Integration Services. Show all posts
Friday, 9 December 2011
Friday, 2 December 2011
Handling NULL values in SSIS fuzzy lookup
I'm using the SSIS Fuzzy Lookup data flow transformation in my current ETL package to find account name matches between source and target. So I compare the similarity of a number of fields using a conditional split transformation like:
But SSIS gives a score of 1 to fields that are NULL in both the source and reference tables. E.g. if the phone number is null in the source table and the reference table phone number is also null then SSIS marks this as an exact match.
(_Similarity > .70 || _Confidence > .70)
|| (_Similarity_CleanedCompanyName > .70 && _Similarity_CleanedABN > .70 && !ISNULL(CleanedABN))
|| (_Similarity_TradingNames__c > .70 && !ISNULL(TradingNames__c) && _Similarity_CleanedABN > .70 && !ISNULL(CleanedABN))
But SSIS gives a score of 1 to fields that are NULL in both the source and reference tables. E.g. if the phone number is null in the source table and the reference table phone number is also null then SSIS marks this as an exact match.
Subscribe to:
Posts (Atom)