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.

Example Script

Once you have created this Matchcode key field, you must populate it with data. An example script:

DECLARE @Hybrid INT, @RetVal INT

SET @Hybrid = dbo.mdMUHybridCreate()
SET @RetVal = dbo.mdMUHybridSetLicenseString(@Hybrid, ‘<License Key>’)
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’).

SSIS Package

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

Configure the 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):

Save the Configuration File

Once you’re sure your settings are correct, go to File | Save as .cfg file… to save this configuration to a .cfg file. The location of this file is not important, just be sure you remember where you save it. A good location is the same one as the MatchUp data files (c:\ProgramData\Melissa DATA\MatchUp). Note that if you’re planning on using the INSERT TRIGGER function, your trigger must have the same name as this file (without the .cfg extension). 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 your first task will be deduping your master table. In this case, you should use this SSIS project to dedupe the table. Since it’s building and saving matchcode keys, you don't need to build keys using the script mentioned earlier.

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

Support

If you do not have SSIS installed or need a License Key to use the MatchUp SSIS Component, please contact us:

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).