MatchUp Object:SQL-CLR:Tutorial

← MatchUp Object

MatchUp SQL-CLR Navigation
Overview
Tutorial
Functions

Master Table Preparation

In all these functions, there is a ‘master table’. This table requires some preparation before it can be used by these functions:

• It must have a field that will contain Matchcode keys. Typically, this is a VARCHAR with a size equal to or greater than the sum of the component sizes listed in the matchcode you plan on using.
• It is strongly recommended that this field has an index.
• Some of the functions can report table’s record Id. If you want to take advantage of this, the field must be an INT. It does not necessarily need to be unique, indexed or auto-incrementing, but most people set up Id fields in this way.

Once you have created this Matchcode key field, you must populate it with data. I think the easiest way would be with a script like this:

DECLARE @Hybrid INT, @RetVal INT

SET @Hybrid = dbo.mdMUHybridCreate()
SET @RetVal = dbo.mdMUHybridSetLicenseString(@Hybrid, ‘<License>’)
SET @RetVal = dbo.mdMUHybridSetPathToMatchUpFiles(@Hybrid, ‘<Path to MU Files>’)
SET @RetVal = dbo.mdMUHybridSetMatchcodeName(@Hybrid, 'Address, Last Name, First Name')

SET @RetVal = dbo.mdMUHybridInitializeDataFiles(@Hybrid)

-- Establish field mappings:
SET @RetVal = dbo.mdMUHybridClearMappings(@Hybrid)
SET @RetVal = dbo.mdMUHybridAddMapping(@Hybrid, 17) -- Zip5
SET @RetVal = dbo.mdMUHybridAddMapping(@Hybrid, 9)  -- FullName 
SET @RetVal = dbo.mdMUHybridAddMapping(@Hybrid, 9)  -- FullName 
SET @RetVal = dbo.mdMUHybridAddMapping(@Hybrid, 14) -- Address

-- Write Matchcode keys to table:
UPDATE mdMatchUp SET
  MatchKey = dbo.mdMUHybridBuildKeyEx(@Hybrid, Zip, Last, First, Address, NULL, NULL, NULL, NULL,
  NULL, NULL)

You should periodically run this script. If a record’s contents change and you don't run this script, the MatchKey will no longer correctly represent that record (what we refer to as ‘MatchKey rot’).

Once you’ve done this, start BIDS and create a new SSIS package. A very simple package, with your master table as an input into a MatchUp component:

Now configure the MatchUp component. First set the Matchcode Name (it better match the one you built all those keys for!):

Then, move over to the ‘Field Mapping’ tab and complete the mappings appropriately (again, they’ll match the script you used earlier):

Move over to the ‘Options’ tab, and specify the matchcode key (again, same field as in the script):

Okay, once you’re sure your settings are correct, go to File | Save as .cfg file… to save this configuration to a .cfg file. I usually locate this file in the same location as my MatchUp data files (c:\ProgramData\Melissa DATA\MatchUp), but you can put it wherever you want. Note that if you’re planning on using the INSERT TRIGGER function, your trigger must have the same name as this file (ie, without the .cfg extension), so in that circumstance you will want to name the file in a way that follows your trigger naming conventions (ie, TR_Master.cfg).

At this point, you’re done with SSIS, you don’t need to run the package or even save it.

There may be cases where the user’s first task will be to dedupe his master table. In this case, he should use this SSIS project to dedupe the table. Since it’s building saving matchcode keys, he needn’t build keys using the script mentioned earlier. 

We also provide examples on how to use the SQL-CLR functions directly in SQL if SSIS configuration is not possible or desired.

  *If you do not have SSIS installed or need a license to use the MatchUp SSIS Component, 
   please contact us with the information below…

For free technical support please call us at 800-MELISSA ext. 4 (800-635-4772 ext. 4) or email us at tech@MelissaData.com.

To purchase MatchUp Object or the MatchUp SSIS Component, please contact our sales department at 800-MELISSA ext. 3 (800-800-6245 ext. 3).