CODEWITHSUNDEEP
sql-serverssis
tempidope
tempidope

Reputation: 873

SQL Server Integration Studio - Fuzzy Matching; Lookup Column doesn't show up

I'm building minor Fuzzy Lookup between two tables on SQL Server 2016.

Table 1 ID INT, NAME NVARCHAR(MAX)

Table 2 ID INT, NAME NVARCHAR(MAX)

Steps following:

  1. Create the DTS Package
  2. Add OLE DB Source to SQL Server Script for the first table "SELECT ID, NAME FROM TABLE1"
  3. Add Fuzzy Transformation
  4. Choose the Reference Table as TABLE2

Now, when I try to add mapping: I can choose the INPUT COLUMN as "NAME" however, the Lookup column shows blank.
If I choose the Lookup column first, it shows the fields but the Input column turns blank

enter image description here

Upvotes: 0

Views: 513

Answers (1)

billinkc
billinkc

Reputation: 61211

My assumption is that the nvarchar(max) portion is what is killing it.

Only input columns with the DT_WSTR and DT_STR data types can be used in fuzzy matching. Exact matching can use any DTS data type except DT_TEXT, DT_NTEXT, and DT_IMAGE. For more information, see Integration Services Data Types. Columns that participate in the join between the input and the reference table must have compatible data types. For example, it is valid to join a column with the DTS DT_WSTR data type to a column with the SQL Server nvarchar data type, but invalid to join a column with the DT_WSTR data type to a column with the int data type.

If you can downsize to nvarchar(4000), then it should work.

https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/fuzzy-lookup-transformation

Upvotes: 1

Related Questions