SSIS fuzzy lookup with multiple outputs per lookup

Question

I have a pretty simple SSIS package with 3 components:

1. OLE DB Source
2. Fuzzy Lookup
3. OLE DB Destination

In the fuzzy lookup component I changed in the advanced tab the "Maximum number of matches to output per lookup" from 1 to 2.

When I run the package after the change I get this error message:

[OLE DB Destination [57]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert]._Confidence'. "

Changing the number back to 1 works again. The OLE DB Source has around 45000 rows and the lookup has around 580000 rows.

I don't know how to address this problem further, so any help is appreciated!

Thanks Daniel

Answer 1

I solved the issue:

The confidence column has to have the data type "float" and not "real".

Daniel