MatchUp Object:SQL-CLR:Tutorial: Difference between revisions

From Melissa Data Wiki
Jump to navigation Jump to search
Created page with "{{MatchUpSQLCLRNav}} {{CustomTOC}} ==Master Table Preparation== In all these functions, there is a ‘master table’. This table requires some preparation before it can be ..."
 
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 10: Line 10:
*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.
*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:
===Example Script===
Once you have created this Matchcode key field, you must populate it with data. An example script:


<pre>
<pre>
Line 16: Line 17:


SET @Hybrid = dbo.mdMUHybridCreate()
SET @Hybrid = dbo.mdMUHybridCreate()
SET @RetVal = dbo.mdMUHybridSetLicenseString(@Hybrid, ‘<License>’)
SET @RetVal = dbo.mdMUHybridSetLicenseString(@Hybrid, ‘<License Key>’)
SET @RetVal = dbo.mdMUHybridSetPathToMatchUpFiles(@Hybrid, ‘<Path to MU Files>’)
SET @RetVal = dbo.mdMUHybridSetPathToMatchUpFiles(@Hybrid, ‘<Path to MU Files>’)
SET @RetVal = dbo.mdMUHybridSetMatchcodeName(@Hybrid, 'Address, Last Name, First Name')
SET @RetVal = dbo.mdMUHybridSetMatchcodeName(@Hybrid, 'Address, Last Name, First Name')
Line 37: Line 38:
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’).
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.


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:
[[File:MU_SQLCLR_Component.png|link=]]
 
 


==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!):
Now configure the MatchUp component. First set the Matchcode Name (it better match the one you built all those keys for!):


 
[[File:MU_SQLCLR_Matchcode.png|link=]]


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


 
[[File:MU_SQLCLR_FieldMapper.png|link=]]


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


[[File:MU_SQLCLR_Options.png|link=]]


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


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


  *If you do not have SSIS installed or need a license to use the MatchUp SSIS Component,
We also provide examples on how to use the SQL-CLR functions directly in SQL if an SSIS configuration is not possible or desired.
    please contact us with the information below…


For free technical support please call us at 800-MELISSA ext. 4
==Support==
(800-635-4772 ext. 4) or email us at tech@MelissaData.com.
If you do not have SSIS installed or need a License Key to use the MatchUp SSIS Component, please contact us:


To purchase MatchUp Object or the MatchUp SSIS Component, please contact our sales department at
For free technical support please call us at 800-MELISSA ext. 4 (800-635-4772 ext. 4) or email us at tech@MelissaData.com.
800-MELISSA ext. 3 (800-800-6245 ext. 3).


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




[[Category:MatchUp Object]]
[[Category:MatchUp Object]]

Latest revision as of 21:43, 19 December 2016

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