Fuzzy Lookup using existing index failed

Question

Hi All,

Please help me to check on this fuzzy lookup issue.

[Situation]

I have selected to store and maintain index for first time, it works fine (just input 4 variables and look-up from table which contains 1 million records). After execution, I altered the option to use existing index to do the same thing but receive the error msg.

[Environment]

SQL Server 2012 RC0 env. & SSDT development env.

[Error Msg]

[Fuzzy Lookup [111]] Warning: Catastrophic failure
[SSIS.Pipeline] Error: Fuzzy Lookup failed the pre-execute phase and returned error code 0x8000FFFF.

I try to reduce my look-up table into 500,000 record, and it works fine by using existing index, but I don't know why??...

Should I change any settings about memory allocation(I set MaxMemoryUsage to 0)? Or may be about RC0 issue?

I have changed WarmCaches property to False but not works.... could anyone give some suggestions?

Thanks in advanced.

BR,

Jack

Answer 1

Hi,

I believe RC is not supported anymore as RTM is out. Please try it with RTM version and if the problem is still exists you should post your DDL, and teh SSIS dtsx xml for us to test it.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Hi Janos,

Thanks for your suggestion, after installed the 2012 RTM version, the package still go wrong with the same error message while using existing index.

And I also found the weird thing when I try to use the drop down list to choose the index which created before, the list always show "Loading...", does anyone could tell me where is the problem here?

Thanks in advanced.

BR,

Jack

Answer 3

Hi Jack,

When you select Maintain stored index on the Reference Table tab of the Fuzzy Lookup Transformation Editor, the transformation uses managed stored procedures to maintain the index. These managed stored procedures use the common language runtime (CLR) integration feature in SQL Server. By default, CLR integration in SQL Server is not enabled. To use the Maintain stored index functionality, you must enable CLR integration. For more information, see Enabling CLR Integration.

Because the Maintain stored index option requires CLR integration, this feature works only when you select a reference table on an instance of SQL Server where CLR integration is enabled.

For more information about Fuzzy Lookup Transformation, please refer to:
http://technet.microsoft.com/en-us/library/ms137786.aspx 

Thanks,
Eileen

Answer 4

Hi Eileen,

Thanks for your information, about enable CLR Integration, I have already set it on as below.

BR,

Jack

Answer 5

Finally I solved this issue by clicking some options of the Comparison Flags in Fuzzy Lookup component.

Just for your reference, http://msdn.microsoft.com/en-us/library/ms141038

Thanks for all your help.

Jack

Answer 6

Hi Jack,

Thank you for sharing your solutions and experience here. It will be very beneficial for other community members who have similar questions.

Thanks,
Eileen

Answer 7

Hi Jack, 

I am experiencing this same issue, can you describe in a little bit more detail what you did with the Comparison Flags to get this to work?

Chris

Answer 8

For anybody else that might see this issue after running the SSIS package once to create the index and then switching it to use the existing index- you have to turn off the WarmCache feature.  I beat my head against the wall for 2 days and all you have to do is change that option.