Previous Article | matchIT SQL Index | Next Article |
4.10.1 msp_CreateKeysTable
WARNING: This stored procedure has been deprecated and will be removed in a future release of matchIT SQL. The stored procedure should no longer be used – except for configurations that have compatibility setting less than 2.0.0 – and existing processes should be modified accordingly.
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.
Setting |
Description |
datasources |
Specifies the database connection, table and column mappings used to define the dataset being processed. The keys table that will be created when this procedure runs will have the same name as the first mapped table with a ‘__keys__’ suffix. |
generalSettings->deleteKeysTableOnGenerate |
When this setting is switched on, the keys table (if it already exists) will be deleted and recreated by this procedure, prior to being re-populated with keys by the BulkGenerateKeys stored procedure. |
outputSettings->keyColumns |
Specifies the columns that the keys table is created with (and populated by BulkGenerateKeys). Note that all key fields used in the match keys (such as the fuzzy and exact match keys if running the relevant stored procedures) must be included in the keys table. |
4.10.2 msp_AddKeyFieldsToTable
WARNING: This stored procedure has been deprecated and will be removed in a future release of matchIT SQL. The stored procedure should no longer be used, and existing processes modified accordingly.
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.
- Table name – specifies the named table that the key columns will be added to.
Simply appends all necessary matching and key fields to the specified table in the database (using the connection string specified in the datasource).
Note that to use this procedure, your XML must specify only one table in the datasources section, and this must also be marked as the keys table.
e.g.
<tables>
<table name="contacts" isKeysTable="true" />
</tables>
4.10.3 msp_GenerateKeys
WARNING: This stored procedure has been deprecated and will be removed in a future release of matchIT SQL. The stored procedure should no longer be used, and existing processes modified accordingly.
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications
This procedure generates the match key field values for all records in the current database (using the connection string specified in the datasource).
We highly recommended using msp_BulkGenerateKeys, with a separate keys table, as it provides the best key generation performance.
The settings are the same as msp_BulkGenerateKeys, except that the matching and key field data is not bulk loaded into an empty keys table. Significantly increasing the time taken for key generation, particularly when used with large databases.
4.10.4 msp_CreateCustomMatchesTable
WARNING: This stored procedure has been deprecated and will be removed in a future release of matchIT SQL. The stored procedure should no longer be used, and existing processes modified accordingly.
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
- Table name suffix – table in which to create the primary key column.
Creates a custom schema of a matches table from the standard matches table as defined in the configuration object for the specified data source. Relies on a matches table existing in the specified data source. The table name defined in the configuration can be given a suffix as the third parameter of the procedure if creating multiple instances to distinguish between them.
4.10.5 msp_CreateCustomGroupedMatchesTable
WARNING: This stored procedure has been deprecated and will be removed in a future release of matchIT SQL. The stored procedure should no longer be used, and existing processes modified accordingly.
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
- Table name suffix – table in which to create the primary key column.
Creates a custom schema of a grouped matches table from the standard matches_grouped table as defined in the configuration file for the specified data source. Relies on a matches_grouped table existing in the specified data source. The table name defined in the configuration can be given a suffix as the third parameter of the procedure if creating multiple instances to distinguish between them.
4.10.6 msp_GroupOverlap
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.
- Level – Matching Level at which to group the records (Individual, Family, Household, Business, Custom).
After running msp_FindOverlap, this will group all matching record pairs into sets of matching records. If you have previously run FindExactOverlap, then we recommend that you copy the matching pairs data from your exact_matches table into you matches table prior to running this grouping step, unless Merge Exact Matches is enabled.
Setting |
Description |
generalSettings->preventBridgedMatches |
When enabled matchIT SQL will attempt to stop match groups containing bridged records such as: J Smith John Smith Julian Smith In the scenario above, both John Smith and Julian Smith match with J Smith, but not with each other. |
generalSettings->masterRecordIdentification |
When this setting is active, matchIT SQL will use the MasterPriority matrix to determine which record in a matching group should be marked as the master record (i.e. the best record). When this setting is off, the record with the lowest unique_reference will be chosen as the master record. |
dataSources->ConnectionString |
Connection string used to connect to the database during processing. |
outputSettings->groupedMatchesTable |
Name of the group matches output table that will be produced during the processing of this procedure. Note that if the overlap attribute is empty, then the name in the name attribute will be used. |
outputSettings->matchesTable |
Name of the matches table containing the matching pairs that will be used as the input source for this procedure. Note that if the overlap attribute is empty, then the name in the name attribute will be used. |
4.7.2.2 Overlap matches_grouped table structure
During processing the stored procedure will output the results to the matches_grouped table (this name can be configured – see above). The structure of the output table is as follows:
Column |
Description |
ID |
Record ID for each matching group. |
Record1 |
Reference ID of the first record in the matching pair (from the Main database) |
Record2 |
Reference ID of the record that is deemed to match Record1 From the Overlap datasource. |
Score |
The Score column is copied from the relevant level’s total score (grouping can only take place on one matching level; to group using multiple levels requires multiple runs of GroupMatches/GroupOverlap). |
MatchRef |
Indicates the unique reference of the master record in the group. In the case of an overlap, the MatchRef column indicates the unique reference of the record from the overlap table; in effect, it’s a simple copy of the Record2 column. |
BaseScore |
Indicates the lowest score of all the matches in the group, normally not relevant to an overlap. |
S4.2.1 - msp_GenerateNCOAAddresses
Input Parameters:
- Configuration file – the file path of the configuration file to be used when this procedure is run.
- Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.
GenerateNCOAAddresses can be used to keep your database up-to-date as your customers move or their addresses are corrected. Note that this is available to licensed users only, and can only process US data.
GenerateNCOAAddresses is available as both a stored procedure and an SSIS task. The process involves sending data from the input table to an online service, and writing the received processed data into an output table for subsequent processing and use. (Please see the Security Protocol for further information.)
Stored Procedure
When running as a stored procedure, the data source is used to configure the input table and field mappings that are passed to the NCOA service. If this will follow GenerateCorrectedAddresses, be sure to use the corrected addresses table and its columns as inputs to the NCOA service.
To use the output NCOA tables and columns in following stored procedures (i.e. GenerateKeys), no further configuration is necessary because the stored procedure will be able to determine the existence of the NCOA table and automatically make use of it.
SSIS Task
When running as an SSIS task, the user has a choice of how the input table and mappings are obtained: from a preceding GenerateKeys task (in which case no extra configuration is necessary), from a preceding GenerateCorrectedAddresses task (in which case only the required name fields need mapping, while the corrected addresses are automatically used), or via manual configuration of the connection string, tables, and columns.
To use the output NCOA tables and columns in following tasks (i.e. GenerateKeys), it is necessary to map the table and its columns in the task.
Setting |
Description |
dataSources |
Specifies the database connection, table and column mappings used to define the dataset being processed. |
ncoa->customerInfo->listProcessor->name |
List processor full name. |
ncoa->customerInfo->listProcessor->street |
List processor street address. |
ncoa->customerInfo->listProcessor->lastLine |
List processor city, state, ZIP. |
ncoa->customerInfo->listProcessor->phone |
List processor telephone number. |
ncoa->customerInfo->mailer->name |
Mailer full name. |
ncoa->customerInfo->mailer->street |
Mailer street address. |
ncoa->customerInfo->mailer->lastLine |
Mailer city, state, ZIP. |
ncoa->customerInfo->mailer->phone |
Mailer telephone number. |
ncoa->output->table->name |
Can be used to specify the name of the table that will be created. If this is left blank, then the table’s name will be automatically generated. |
ncoa->output->optionalFields |
Any of these fields can be written to the output table. (See Optional Output Fields.) |
ncoa->options->useMixed |
If this is set to "true", data is returned in mixed-case format. If this is set to "false", result data is returned in all upper case characters. |
ncoa->options->useAlias |
If this is set to "true", alias street names are returned when they are used in the input. If this is set to "false", 'official' street names are returned even when alias street names are used in the input. (An alias street name is an alternative name for a street that is acceptable to the USPS. It may be a name by which a street was formerly known, a commonly-used nickname for the street, or one the community prefers to use.) |
ncoa->options->validAddressesOnly |
If this is set to “true”, and GenerateCorrectedAddresses has been run beforehand, then only valid addresses (with a score of 0) are processed by the NCOA service. |
ncoa->options->blockSize |
Data is uploaded to the NCOA service in ‘blocks’. This setting specifies the maximum number of records per block. |
ncoa->options->timeout |
The maximum time to allow for every 100,000 records processed, in minutes (the default is 15). This should be increased if you have a slow internet connection or are experiencing timeouts. Timeouts can be disabled by specifying 0. |
ncoa->options->retries |
The maximum number of retry attempts when processing has failed (the default is 1). Specify 0 to disable retry attempts. |
S4.2.2 - msp_GenerateNCOAAddresses - Inputs, Outputs and Reports
4.2.2.1 Input Fields
The following fields must be input to the NCOA process, and will be written to the output table:
Name |
Description |
FullName or |
Fullname field. Optional if an organization is specified |
FirstNames and LastName |
If FullName is not used, then both these fields are required. |
Organization |
Optional. Can be used to specify the company name. |
Address1 |
Sets the “Street” field of the lookup record. Use this field type to pass all information relating to the primary street address, including the street name, house number, directional’s, and street suffix. You can also use this field type to pass all other acceptable forms of primary address information, such as PO Box numbers, rural route numbers, and highway contract numbers. In addition, suite and apartment information can be passed with primary address information through the “Street” field. For example, “123 Main St” and “123 Main St, Apt A” are both acceptable. |
Address2 |
Optional. Can be used to specify any suite and apartment information, if these are in a separate column from the street part of the lookup address (see Address1 above). |
Town |
Specifies the city. |
Region |
Specifies the state. |
Postcode or |
Specifies the 9-digit ZIP code. |
PostOut and PostIn |
Specifies the 5-digit ZIP code and the ZIP+4, if these are in separate columns. |
4.2.2.2 Output Fields
The following fields are written to the output table:
Name |
Description |
ncoaFullName or |
Only if the fullname was specified on input. |
ncoaFirstName and ncoaLastName |
Only if the fullname was not specified on input. |
ncoaCompany |
Optional. Retrieves the company name. This is only output if Organization was passed in via the input fields, or if this optional field is enabled (see Optional Output Fields). |
ncoaStreet |
Retrieves the street part of the output address. |
ncoaSuite |
Optional. Retrieves the suite or apartment information of the output address. This is only output if Address2 was passed in via the input fields, or if this optional field is enabled (see Optional Output Fields). |
ncoaCity |
Retrieves the city from the output address. |
ncoaState |
Retrieves the output state abbreviation. |
ncoaZip |
Retrieves the output ZIP Code. |
4.2.2.3 Optional Output Fields
Any of the following fields can be written to the output table:
Name |
Description |
ncoaCOACode |
This field will be populated with the return codes, see NCOA Return Code section for the description of codes returned. |
ncoaCoaFound |
This field is a True/False flag that tells whether an address change was found. |
ncoaMoveEffectiveDate |
Date of move. mm/yyyy |
ncoaCompany |
Retrieves the company name that was entered through the input “ncoaCompany” field, if any. AccuMail Move™ will also verify company moves based off this the company field. |
ncoaSuite |
Retrieves output secondary street information, such as suite and apartment information, if such information was entered through the input “ncoaSuite” field. |
ncoaPlus4 |
Retrieves the output ZIP+4 Code. The ZIP+4 Code is the 4-digit extension only. You can retrieve the 5-digit ZIP Code using the ncoaZIP field type described earlier in this table. |
ncoaUrbanization |
Retrieves output urbanization information for Puerto Rican addresses. |
ncoaDP |
This string consists of a 2-byte delivery point code |
ncoaCRRT |
Retrieves the output carrier route code. This is a 4-digit code assigned to each address on a mail carrier’s route. |
ncoaLotCode |
Retrieves the output Line of Travel identifier consisting of a 4-digit number, plus a 1-character sequence code (either "A" for Ascending or "D" for Descending). The 4-digit number indicates the order in which delivery will be made within a given ZIP+4. The 1-character sequence code indicates whether delivery will be made in ascending or descending order. Once a LOT code is appended to your data file records, you can use it to presort your mailings so that they qualify for Enhanced Carrier Route rates. |
ncoaLotDir |
The order in which the mail carrier delivers mail within a given carrier route. When you include the Line of Travel information, your mail may be eligible for the USPS Standard Mail Non-Automation Basic Enhanced Carrier Route Presort Rate. |
ncoaCountyNumber |
Retrieves the output county number. This is the 3-digit USPS code for the county in which the address resides. |
ncoaCountyName |
Retrieves the output county name. |
ncoaCongDistrict |
Retrieves the output congressional district code. This is a 2-digit identifier for the United States congressional district to which the input address belongs. |
ncoaLACS |
A 1-character Locatable Address Conversion Service (LACS) code to identify records that have been converted to the LACS system. The LACS system is being used for many rural route addresses and city addresses that are being modified to city style addresses so that emergency vehicles, such as police cars and ambulances, can more easily find these locations. |
ncoaAcsKeyline |
Retrieves the output Address Change Service (ACS) keyline. The ACS keyline is a code the USPS uses to uniquely identify any address in the United States. You can use the ACS keyline to match the records in your mailing list with the list of ACS notifications in the USPS’s ACS fulfillment files. |
ncoaHouseNumber |
Retrieves the house number for the output street address. For example, if the output street address is “123 Main St,” the house number is “123.” |
ncoaPreDirectional |
Retrieves the pre-directional for the output street address. For example, if the output street address is “123 E Main St,” the predirectional designator is “E.” |
ncoaStreetName |
Retrieves the street name for the output street address. For example, if the output street address is “123 Main St,” the street name is “Main.” |
ncoaStreetSuffix |
Retrieves the street suffix for the output street address. For example, if the output street address is “123 Main St,” the street suffix is “St.” |
ncoaPostDir |
Retrieves the post-directional for the output street address. For example, if the output street address is “123 Main St N,” the postdirectional is “N.” |
ncoaSUD |
Retrieves the secondary unit designator (SUD) for the output street address. For example, if the output street address is “123 Main St Apt 12,” the SUD is “Apt.” |
ncoaUnitNum |
Retrieves the unit number for the output street address. For example, if the output street address is “123 Main St Apt 12,” the unit number is “12.” |
ncoaLeftovers |
Retrieves any leftover information that was part of the input street address string, but was not used for obtaining a match. Leftover information is input data that Accumail had to discard to correct the address. For example, if the input street address is “123 Main St Rubbish Here,” the output leftover information is “Rubbish Here.” |
ncoaPMB |
Retrieves the output Public Mailbox address |
ncoaDPV |
Retrieves the 3-byte DVP (Delivery Point Verification) match codes. The match codes indicate whether or not the address is valid (and if not, why not), whether or not the address is within a Commercial Mail Receiving Agency (CMRA), and whether or not the address was flagged as a False Positive. The “DPV” field returns a separate code in each position of the 3-byte result string, as follows: BYTE 1 blank – The address was not coded by AccuMail and therefore no DPV processing was performed. Y – All delivery point components of the address were DPV validated. D – The address’s building number was DPV validated, but required unit-level information is missing. S – The address’s building number was DPV validated, but the unit number is invalid. N – The address’s building number is invalid. BYTE 2 blank – The address was not coded by AccuMail and therefore no DPV processing was performed. Y – The address was found in the CMRA (Commercial Mail Receiving Agency) table. N – The address was not found in the CMRA (Commercial Mail Receiving Agency) table. BYTE 3 blank – The address was not coded by AccuMail and therefore no DPV processing was performed. Y – The address was found in the False Positive table. N – The address was not found in the False Positive table. |
ncoaDPVAnswer |
blank – The address was not coded by AccuMail and therefore no DPV processing was performed. Y – All delivery point components of the address were DPV validated. D – The address’s building number was DPV validated, but required unit-level information is missing. S – The address’s building number was DPV validated, but the unit number is invalid. N – The address’s building number is invalid. |
ncoaDpvCMRA |
Commercial Mail Receiving Agency |
ncoaDpvFalsePositive |
This is a seed table used by the Delivery Point Verification Service (DPV). It is used by the service to guard against the possibility of mailers manufacturing artificial mailing lists from the data in the DPV database of every valid delivery point in the U.S. |
ncoaDpvFootnotes |
Retrieves the 8-byte DPV (Delivery Point Verification) Footnotes string. This field returns up to four 2-character codes that supplement the DVP field codes (described above) providing additional information about the DPV match/mismatch. Up to four of the following 2-character codes will be returned: AA – The address was successfully coded by AccuMail. A1 – The address was not successfully coded by AccuMail. BB – All components of the address were DPV validated. CC – The address’s building number was DPV validated, but the unit number is invalid. N1 – The address’s building number was DPV validated, but required unit -level information is missing. M1 – A building number is missing for the input address. M3 – The address’s building number is invalid. P1 – The input address is missing a required PO, RR, or HC Box number. RR – The input address was identified by DPV as a Commercial Mail Receiving Agency (CMRA). R1 – The input address was identified by DPV as a Commercial Mail Receiving Agency (CMRA), but required unit-level information is missing. |
ncoaLastLine |
Retrieves the output ‘last line’ string. This output ‘last line’ string is a formatted city/state/ZIP string that includes the correct ZIP+4 Code. For example, the ‘last line’ information for Datatech SmartSoft is “Agoura Hills, CA 91301-4301.” |
ncoaMoveType |
Indicates the move as Family, Business, or Residential. |
ncoaResult |
Retrieves the output error code. AccuMail assigns an error code if the input record could not be found in the USPS National Database. This is a 2-byte code that identifies what was wrong with the input address and why AccuMail could not match it. If AccuMail matched the input address successfully, then this field returns a blank string. |
ncoaCorrections |
Retrieves the output correction codes string. This string consists of single character codes that indicate the corrections AccuMail had to make to the input record. |
ncoaErrorMessage |
Retrieves the message text associated with the error code that AccuMail assigned if the input address could not be matched. This is a descriptive sentence or paragraph that describes the reason AccuMail could not correct the input record. If AccuMail matched the input address successfully, then this field returns a blank string. |
ncoaWasDigitCoded |
Returns a 1 for addresses that successfully coded with a plus4. Returns a 0 for uncoded addresses. |
ncoaWasCRRTCoded |
Returns a 1 for addresses that has returned with a Carrier Route or a 0 if no Carrier Route was returned. |
ncoaWasPlusCoded |
Returns a 1 for addresses that successfully coded with a plus4. Returns a 0 for uncoded addresses. |
ncoaWasDPBCoded |
Retrieves the output delivery point barcode string. This string consists of a 2-byte delivery point code plus a 1-byte checksum digit. These constitute the values required for producing a Delivery Point Barcode. |
ncoaWasDPVCoded |
Returns a 1 for addresses that were successfully DPV coded or a 0 for addresses that did not pass DPV validation. |
4.2.2.4 Reports
Three reports are created after NCOA processing:
- CASS Certificate (PDF);
- NCOA Details Report - this is a text file that will list all matches found;
- NCOA Processing Summary Report (PDF).
When processing has completed, the reports are moved to the reports folder (as specified by the path attribute of the outputSettings/reports node in the configuration file).
Previous Article | matchIT SQL Index | Next Article |