Fuzzy Matching - Address Cleansing

Hi *,

does anyone know if MS supports some kind of breaking strategy within Fuzzy Lookup/Grouping?

Besides that, I'd like to perform a address cleansing operation on a CRM database. I don't have a reference table (Street, Zip, LastLine, etc.) for that. Where can I get an appropriate database? Anyone has some experience with this issue?

Thanks a lot.
S.Is there no one who uses SSIS for address validation?!|||

I don't really understand what you mean by a breaking strategy, do you mean splitting an address out into its component parts, such as street, city, post code? If so then there is nothing in the stock components that does this. You would really want some third-party address verification software. Many of them will have their own API that could then be used to integrate this into a SSIS data flow, in fact we did this for a client recently. They key to provide effective address cleansing is the reference system, more than just a table of data really. If this is a one off or an infrequent requirement then I would just use a bureau type service rather than anything integrated into SSIS, but on the other hand if this is an ongoing requirement then purchasing a product and integrating it would make sense.

The Fuzzy Lookup could be used to try and find a match between an existing table of addresses and a source address, but unless you are looking for existing customers or such like, you would have to purchase an address "file". That is generally what you are paying for in a third-party address product, so I think it would make sense to actually use the address software API rather than the Fuzzy.

|||My experience is that many 3rd party data quality tools (i.e. Firstlogic IQ) have some very impressive features like the mentioned breaking groups (breaking the source data into several parts and perform cleansing only within these parts --> saves a lot processing time).

I think the fuzzy search capabilities of SSIS are really powerful. Other products, like the mentioned firstlogic iq, doesn't perform very well in this respect. So I was thinking why not use SSIS and a comprehensive postal database to take advantage of both worlds. My biggest concerns are a really big slow down in performance and the lack of parsing features.

Regards, S.|||Ok, let me get this straight. Nobody ever tried to use fuzzy matching for adress verification?! What do you use fuzzy matching for instead?|||Have you looked at Intelligent Search Technology SSIS components. Their fuzzy matching components are really good. They also provide address correction and all of that within SSIS. The link is: http://www.intelligentsearch.com/ssis-data-quality/index.html

Fuzzy Lookups and Groupings Algorithm

Hello,
I'm trying to clean my data using fuzzy lookup algorithm though SSIS, but i get null values everywhere. This is what i did:

I applied the fuzzy lookup in a table (tblValues). As source table i have the tblValues, and as reference table in Fuzzy Lookup i have the tblValues as well, resulting null values in all fields/columns.

Do i have to create my own reference table? If yes, how do i do that and what values will i have in this table?I didn't understand how the reference table must be in order the algorithm to work. Any suggestions?

Thank you in advance!

I'm not sure I understand what your objective is. If you are trying to remove duplicates in tblValues, you're better off using a fuzzy grouping task and not a fuzzy lookup task. See the following article for how to use both if you haven't read it yet.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

|||

Thanks for the article i have already read it and i used it as reference to my project. My objective is to fill the null values with the most possible value.

I'm not sure if fuzzy does that, but in that articles says that "Fuzzy Lookup matches input records that are "dirty" (because of misspellings, truncations, missing or inserted tokens, null fields, unexpected abbreviations, and other irregularities) with clean records in a reference table. ", but i didn't understand how the reference table must be.

Fuzzy Lookup/product level is insufficient

I installed SQL Server 2005 Standard Edition on my XP sp2 workstation, developed an SSIS package, which has run for a week on my workstation.

I installed SQL Server 2005 Standard Edition on a Win2003 sp1 server, created the dtsx and install manifest on my workstation, and installed it on the server.

If I run the package on my workstation, pointed to the server database it runs fine.

If I run the dtsx on the server, it fails with the error:

The product level is insufficient for component "Fuzzy Lookup on Name" (83)

Yes, I have SSIS installed on both machines, I have verified that I have the SSIS service on the server.

Any Ideas?

Thanks!

BobP

Bob,
Fuzzy Lookup/Fuzzy Grouping components are part of the Advanced Transformations that are only available in the Enterprise Edition of SQL.
Per MS all transformations/features are available in the BIDS when you are developing. So you really need to becareful about what features you use in your packages.
Larry Poep|||

Thanks
BobP

Fuzzy lookup transform row scores 'inconsistent' with individual column scores

I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.

The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.

For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.

Match 1 Match 2
-- --
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0

_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332

In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.

Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?

Fernando Tubio

Can't even begin to describe it in my own words. This article describes the Fuzzy Math real well. Don't know if you've seen it.

http://msdn.microsoft.com/msdnmag/issues/05/09/SQLServer2005/

|||

Thank you Martin.

I've read the article and it explains the lookup process well. Unfortunately it doesn't answer my question. Specifically, having found two matches, why does the matching algorithm discard what appears to be a better match, at least judging from individual column similarity scores.

I am trying to understand the mechanism to determine if there is anything I can tweak in order to force the algorithm to make a better choice.

Fuzzy Lookup task - expressions for MatchIndexName and ReferenceTableName

Why is there no Expressions collection for the Fuzzy Lookup task? Is there another good way to dynamically configure the ReferenceTableName and MatchIndexName properties?

I beleive Fuzzy Lookup was built before we had expressionable properties in data flows.

You may consider requesting this feature on the connect site.

Thanks,

-Bob

fuzzy lookup taking too much time

I have a SSIS package where a small table of 270 rows are fuzzy looked up with a table in another sql server and inserts the records to a temporary table. This takes more than 3 hours in debug mode or so and never goes beyond this step.I have used a OLE DB destination to insert to temporary table and temporary table doesn't get a value.

How big is that other table? The fuzzy will build an index to start with, and this can take time. Review the options on the second tab to influence the index. Also check obvious things like blocking or such like? What about a profiler trace, anything untoward?

|||other table also is the same size and has same no of rows.Thank for the clues I found that some of the NOT null columns in the destination table were not mapped which result in failure of insert. Now it inserts correct rows to temp table and fails with a primary key violation which means its going in a never ending sort of a loop. I am going to put a profiler trace to identify the problem.Any advice welcome

Fuzzy Lookup problems.

Fuzzy lookup seems to be causing some problems to me. It seems to work at times and doesn't at other times. It would work a couple of times fine and give me the desired results but then without changing anything in the dataflow or the data the next few times it would not run at all and fail the pre-execute of the.

Now I'm currently getting the following error:

[Fuzzy Lookup [248]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [233]. ".

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 249 on component "Fuzzy Lookup" (248) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup" (248) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Any help would be appreciated.

It looks to be a connectivity issue. My first avenue of investigation would be to use Profiler to see if you can see the connection attempt coming into SQL Server.

-Jamie

sql

Fuzzy Lookup problems

Hi everyone,

Ive just started looking at the Fuzzy Lookup feature and i think i must be getting something fundamentally wrong. I have two tables - each contain different meta data representations for a set of potentially similar documents. The only chance i have of matching a document in table A to a document in table B is a common title field. However, manual input means that the titles may differ in both tables although they are potentially quite similar in most cases.

In the lookup i get to specify the output columns from table B (Reference) which is fine, but i don't seem to get to choose the columns from table A that i would also like to see. So my output shows me all the documents from table B that it thinks are similar to ones in table A...but not identifying which record it's similar to.

I initially thought that the "pass through" columns that i identified would appear in the output - but this does not seem to be the case.

I must be using it incorrectly, but i have no idea how to progress with this apart from creating a new source table (C) which is a full outer join of table A and B - and then also using table C as the reference table, but that seems madness.

any help would be appreciated - ta

Andrew

I may have spoken too soon.

It's strange, but if i've been using the 'Advanced Edit' dialog to setup the lookup - which i had assumed contained the same functionality (plus more) as the 'Edit' dialog. It looks like i was wrong. If i setup the Lookup through the Edit page, I can specify the Pass Through columns (which it doesn't look like you can do from inside the Advanced Edit box). Then once i have finished the basic edit i can go in and perform any additional advanced editings.

I suppose it kind of makes sense - my intial attempts at setting Pass Throughs must have been flawed in some other way :)

Hope this helps someone else!

Andrew :)

|||

Perhaps have a look at: http://msdn.microsoft.com/sql/bi/integration/default.aspx?pull=/library/en-us/dnsql90/html/datasol.asp

Although primarily about Master Data Management (MDM) it also covers some of the interesting Fuzzy techniques that can be used with SSIS.

Donald

Fuzzy lookup match issue

Hello,

I have a peculiar problem in my project. My project design is like this

The number in (...) are count of records.

File feed (1000)

|

|

Fuzzy Lookup

against Table2

|

|

Split Fz Lookup results

(_Similarity >= 0.60 && _Confidence >= 0.85)

| |

| |

| Write matches to Table1 (250)

|

Fuzzy Group

Remaining rows (750)

|

|

Split Fz Group results

| |

| |

Write Canonicals Write Dupes

to Table2 to Table1

(300) (450)

This is basically a customer de-dupification project.

The Table2 has the canonicals and Table1 has the dupes (of the canonicals).

I already have some data in these tables and the new data is matched against the existing data

in these tables and classified as new customers and duplicate customers.

In the above process one could notice that the rows identified as dupes of already exsting canonicals

by the Fuzzy Lookup task are written into the dupes table (Table1) and will not be processed further down

the line in the project.

But in my case I see that those matches identified by Fuzzy lookup are further being included in the

Fuzzy Grouping also.

When I run this in debug mode in BIDS, it shows the correct numbers as I have depicted in the

illustration above. But, after execution, when I query the tables it shows that all 1000 rows

went through Fuzzy Grouping.

Any thoughts?

Btw, is there anyway to upload attachments to the postings here?

I also tried introducing a Derived Column between the 'Split Fz Lookup Results' and 'Write matches to Table1' to write some string into one of the table columns. It did not.

Fuzzy lookup error when adding additional lookup columns

I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.

It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:

Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.

These errors occur regardless of what columns I am attempting to add to the lookup list.

I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.

Any ideas on what else could be causing this?
Have you tried deleting the component and recreating it?|||Yes, I just tried deleting and recreating the fuzzy lookup, and the same errors are occuring.
|||

Uh! there is a similar issue reported in Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=266185

but no response from MSFT.

You may want to add your vote

Fuzzy Lookup error - Multiple Identity Columns?

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.

Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.

It's like it's trying to add it's own Id to the temp table created for the tokens

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

I have found an interesting solution to this problem, but I'm still left wondering if this is by design or error. The reference table must be in the dbo schema to work. I began with a reference table in a schema owned by me. I finally moved to another DB on the same server and it worked. The difference I then noticed was that the table was now in dbo. I went back to the original development DB and changed ownership on my schema to dbo. I ran the package and got the same error. So I then moved the table to dbo and it worked just fine. If anybody knows why this might be, I'd really like to know. If you'd like to see where I started out, get a copy of Hands-On SQL Server 2005 Integration Services by Ashwani Nanda. In Chapter 10 there is a good section on removing duplicates.

Unaswered questions

Is it a permission issue, a DB confituration issue or the design of the Fuzzy Lookup or an error.

It is interesting that if I remove the ID column (which uses identity(1,1))from my reference table in my schema it will work, but its sort of the point to be able to go back to the duplicate records for updates, inserts or deletes etc.

Fuzzy Lookup Error

Hi

I get the following error when I use Fuzzy Lookup in a Data Flow task with TransactionOption property set to “Required”

[Fuzzy Lookup [61]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot create new connection because in manual or distributed transaction mode.".

When I Change the TransactionProperty to “Supported” it works fine.

I need the property set to Required for it does an undo in the event of a failure.

Any ideas on how to get the Fuzzy Lookup to work

Are you executing the package containing the data flow task from a parent package? I have seen an issue where the OLE DB connection does not properly defect from the transaction, and throws an error like you're seeing here. The work around was to execute the child package out of process.

~Matt

|||i dont have any child packages..|||

Set Required on the package container, and Supported on all child containers. Then they will automatically enlist with the transaction from the package container.

Why do you need a transaction around a lookup?

sql

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Fuzzy Lookup and Case

Hi,

Could someone please help!

Im doing a fuzzy lookup based on 3 fields (Surname/DOB/Gender). The only difference between the two sets of data is the case of the first letter of the Surname.

Reference table has "Stuart" Lookup has "stuart", I have set Fuzzy Lookup Input for Surname to Ignore Case but still it won't match.

The DOB/Gender are Exsactly the same.

Why does this not work? I there a work around?

Many Thanks, Deano

This isn't a "fuzzy" workaround, but for the scenario you describe it may well work.

Try adding explicitly lower-cased versions of your comparison columns to your data flow source and to the lookup query. Use the standard Lookup transformation, with the lower-cased columns being used for the lookup matching. This will allow you to perform a case-insensitive match without the overhead of the Fuzzy Lookup transformation. If there is a match, send the rows down your data flow path. If there is not, send the rows down an error path, and then use the Fuzzy Lookup only on the error rows.|||No, still not working, I have looked at the data and converted them all to they same type (Unicode String/ String etc both now varchar(50) ) and they still don't match up .It works if I group all the datain one import but if import one of the records into the database then try and import the other it does not match (Same logic). I can't help but thing it is a data type issue but I can't see any difference.

Someone please help!

fuzzy lookup

Hi Friends,

Can some body briefly explain me what is the difference between fuzzy lookup and fuzzy grouping?

thanks and regards

Ruther wrote:

Hi Friends,

Can some body briefly explain me what is the difference between fuzzy lookup and fuzzy grouping?

thanks and regards

One is a lookup, the other is a group by? The fuzzy grouping is good for finding duplicates while the lookup is good for finding matches.|||

If the fuzzy lookup is analogous to the regular LOOKUP component in SSIS then Fuxxy Grouping is most analogous to the AGGREGATE component.

Under the covers the Fuzzy Grouping does the same as the Fuzzy Lookup, it then groups the data according to the looked up value (I think).

BOL will have all the answers.

-Jamie

sql

Fuzzy Lookup

How is it possible to get the Unmatched records on the error path of lookup so that they may be passed to a fuzzy lookup. I'm trying to duplicate the 1st example on the Data Cleansing Webcast.

All I'm getting is a null on the title out of the lookup component.

TIA,
GUYCheck out method 2: http://www.sqlis.com/default.aspx?311|||Can you post a link to the SSIS data cleansing webcast?

|||Here you go.. http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20050512SQLServerDF/manifest.xml

Fuzzy Grouping errors

Hi - we have been evaluating using Fuzzy Grouping and Lookup for maintaining our large list of customer records. Initial testing with Grouping on about 300K records went great but now with a larger sample of 7.3 million records we are running into problems. It doesn't appear to be system limitation - the index is built reasonably quickly and without errors but when it starts the matching we get these errors:

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup" (86) failed with error code 0x8000FFFF. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8000FFFF.

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[Fuzzy Grouping Inner Data Flow : OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[Fuzzy Grouping Inner Data Flow : DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

One thing we did find is that our test server didn't have SP1 installed and that seemed to help a lot (we were getting buffer errors prior to SP1). One other note - the desination table is populated with all the data but no scoring has been applied to it.

Does anyone have any ideas what could be causing this?

Thanks!

Keith Doyle

Had the same problem, and thanks to you, I managed to get this running by doing multiple passes on around 200k rows in each pass.

I also get lots of errors that say"Buffer Manager found Virtual Memory Low but unable to swap out any buffer". The package works inspite of these errors when rowcount is low enough.

DTSDebugHost.exe consumes a lot of Memory during processing. LSASS also seems unnaturally high. I added a lot of Paging memory and that seemed to improve things to the extent that DTSDebugHost usage increased even further :-). But from the PerfMon stats, it looks to me like DTSDebug isnt using paging memory. The other processes actually released memory instead.

I use SQL2005Ent SP1 on Win2003R2 - on Intel X-64.

I hope and pray that there is a fix on the way. Bcos I found Fuzzy quite useful.

|||

I tried out a few things:

1. Added RAM

2. Used DTEXec instead of running in Debug mode in BIDS

3. Reduced columns

Have so far succeeded in 600k rows and am running 1.5 million now, and havent encountered any Buffer Manager errors so far (WIP, and fingers crossed)

My perfmon observations are however repeated. I have a suspicion Fuzzy does not use File Paging efficiently (or not at all). I had more than 8 GB of pagefile space allocated, but my Memory utilization never went beyond Physical Ram. If anything, all other running processes got squeezed out.

I have run some heavy mining stuff on the same infrastructure, without any crashes(even before I added RAM). So why is Fuzzy Grouping crashing? Or is it SSIS?

Another observation - Memory usage did not drop back after closing Devenv or DTExec. Memory gets freed only after a machine restart.

I hope someone at Microsoft tells us what the problem is. And gives us a fix please!!!

|||

Great info - I am seeing the exact same thing. See the other thread I opened for "Fuzzy Grouping: Any success with > 3 million records?". I'll post my latest test results there - please copy your info from this thread to that one.

Thanks!!

|||

I'll have someone investigate at this end and we'll get back to these threads. Thanks for the grat info.

One thing would be to try executing using DTExec outside the debug environment. Or try "Start without Debugging." I doubt this will solve the problem entirely, but it will reduce any effect of the IDE on the problem.

Donald

|||

Please see the follow up to this thread at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417880&SiteID=1&mode=1

sql

Function to lookup row in dataset?

Is there a function to lookup a row in a dataset? Say, find the row where
column x is equal to 10?
Find(Fields!ProductNumber.Value, 10, "Category")
--
McGeeky
http://mcgeeky.blogspot.comNo, but what is it that you are trying to do. There might be another way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Is there a function to lookup a row in a dataset? Say, find the row where
> column x is equal to 10?
> Find(Fields!ProductNumber.Value, 10, "Category")
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||I will be returning a small data set with a known number of rows in it. I
don't want to display the data in a table but rather selectively pick values
out of the data set and display them in various parts of my report.
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:#5jSf8ldFHA.3184@.TK2MSFTNGP15.phx.gbl...
> No, but what is it that you are trying to do. There might be another way
to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> > Is there a function to lookup a row in a dataset? Say, find the row
where
> > column x is equal to 10?
> >
> > Find(Fields!ProductNumber.Value, 10, "Category")
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>