March 23, 2012 at 9:49 am
Hi All
I have a Fuzzy Lookup step in an SSIS package that I'd like to quicken by using the Store And Maintain Index option within the Fuzzy lookup properties, but after changing the step the job fails with the following error:
Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [85]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.
I've noticed that the table index has been created, but the triggers on the reference table have not.
I suspected the problem might be due to permissions, but I'm using an account with system administrator privileges, and the DB Owner is the same account.
CLR Integration has been enabled and I'm on SQL 2008 R2.
Does anyone know what the problem may be?
Any assistance would be appreciated.
May 22, 2012 at 4:27 am
Still struggling with this if anyone has any suggestions.
June 15, 2012 at 4:54 am
So this error turned out to be a problem with the amount of columns being used in the Fuzzy Lookup. It seems there is a limit of 10 columns that can be used, I was using more.
The corruption error is obviously a very unhelpful red herring.
Bearing this in mind, I am able to successfully create the maintained index within the Fuzzy Lookup.
However, when I re run the package, I get the following error:
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Maintenance trigger already installed on this reference table.
System.Data.SqlClient.SqlException:
Obviously the maintenance trigger is already installed on the table as it has already been run previously. I've created other simpler packages with more straight forward Fuzzy Lookups and re run the job a number of times without getting this error. I've noticed that all the Index objects are renamed or dropped and recreated each time it runs.
Is the error that I'm receiving to do with the number of columns I am using in the Lookup again?
If so, is it possible to script the creation of the maintained objects and simply select to Use Existing Index within the Fuzzy Lookup properties? Looking at Profiler it seems that the creation is rather complex, as it creates objects and populates them too. Although whether it populates from trigger calls I'm not sure.
Any help here would be appreciated, because this is proving to be very frustrating.
June 20, 2012 at 9:34 am
For anyone who comes across this problem themselves, the problem seemed seemed to stem from the DB Schema that the reference table belonged to.
The table was in a user defined schema, and seemed to be having trouble dropping the trigger from the table (the trigger is dropped and recreated every time the Fuzzy Lookup is run).
Since it wasn't dropped, it failed to recreate it. Changing the schema to dbo resolved the problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply