March 20, 2007 at 11:31 am
I am using the fuzzy lookup to search for duplicate records. I am able to get it to work on most tables, but for the table I need to use it I am having this error. Has anyone seen it or do you know how to resolve. I can make it work by removing the one identity column on the lookup table, but that's not a working solution for this project. Here is the error
Error: 0xC0202009 at Removing Duplicates from Enrollment Extracts, Fuzzy Lookup [3155]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070320_12:29:13_1428_d32f7a56-ffe4-450a-be8e-38ff51339401'. 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 Removing Duplicates from Enrollment Extracts, DTS.Pipeline: component "Fuzzy Lookup" (3155) failed the pre-execute phase and returned error code 0xC0202009.
March 26, 2007 at 10:09 am
Are you using fuzzy lookup or fuzzy grouping?
Your stuck with 1 ID column. Could you build a view and concatenate the fields, then rip apart after the grouping?
March 27, 2007 at 11:42 pm
Fuzzy lookup, but I have example code from the book hands on sql server integration services that works just fine-the only difference is that I've loaded it on my local instance of SQL Server not one of our development servers. That being the case, I don't know enough about how the server config could affect a package. I have switched to other tasks for the mean time, but I will be coming back to this later this week. Your idea was somewhat similar to my solution, I was going to create an ID in an ETL staging table and then load an int field (no identity) with the RID. After the dupes lookup, I would still have the ID if necessary. My main goal was to be able to trace incoming records to their final resting place. It's a new process I'm working with and I'd like to QA the whole thing for awhile before releasing it on a prodution environment. The thing that messes with my head is why the example code works just fine with an identity column and mine doesn't. C'est la vie......any help is greatly apprecieated. There was one post about this on the SSIS forums at MSDN, but no resolutions.
April 5, 2007 at 7:51 am
I Was able to solve the problem by moving the reference table the the dbo schema. For some reason having this in my own schema didn't work. I changed the ownership of my schema to dbo and got the same error. But no problems with it in dbo. I'm not sure that I completley understand what it has to do with the error message, but I'd really like to know if fuzzy lookup works this way by design or if it's an error.
September 8, 2008 at 9:26 am
There is a simple fix (not very elegant tough from a data model perspective) : in your table you create a calculated column which is a copy of your identity column and you use this copy instead of the original identity column in your fuzzy lookup.
Laurent
Laurent
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply