Difference between revisions of "SSIS:SmartMover:Output Columns"

From Melissa Data Wiki
Jump to navigation Jump to search
Line 42: Line 42:
;Move Type Code
;Move Type Code
:This code indicates the type of address record matched. For a list of these codes, see [[Result Code Details#SSIS:SmartMover Component|SSIS SmartMover Component Result Codes]].
:This code indicates the type of address record matched. For a list of these codes, see [[Result Code Details#SSIS:SmartMover Component|SSIS SmartMover Component Result Codes]].
;NCOA Return Code
:The SmartMover component includes a new field, NCOA Return Code (for U.S records only). The NCOA Return Code indicates the level of matching between the record and the NCOA Link database. For a list of the NCOA Return Codes visit: [[Result Code Details#SSIS:SmartMover Component|SSIS SmartMover Component Result Codes]]





Revision as of 00:19, 8 November 2022

← SSIS:Data Quality Components

SmartMover Navigation
Overview
Tutorial
Advanced Configuration
Melissa Data Cloud
SmartMover Tabs
Input Columns
Output Columns
Processing Options
Pass-Through Columns
Output Filter
Result Codes
Result Codes



The output section consists of various sets of return fields. Output Name returns the concatenated and or parsed Name parts of the input Name. Output Address returns the new change of address data or for non-moves, standardized address information. Additional Output Columns can be used to store additional address info and parsed address parts.

New field names can be created by typing the name into the box.

SSIS SM OutputColumns1.png

Use Input Fields For Output
Selecting this option will automatically map your input Name and Address fields as the output fields, hence overwriting the original structure with new data. Caution: selecting this option will wipe out any new Output fields you have just created.


Output Name

FullName
This field returns the standardized FullName used to look up the change of address.
Prefix
This field returns any part of the name that precedes the given name, such as "Mr.," "Ms." or "Dr.," for each full name detected.
First Name
This field returns the given name for each full name detected.
Middle Name
This field returns the middle names or initials for each full name processed.
Last Name
This field returns the family name for each full name processed.
Suffix
This field returns any part of the name that follows the family name, such as degrees ("MD" or "PhD") and generational indicators ("IV" or "Jr."), for each full name processed.


Move information

Effective Date
Map this field which receives the effective date (yyyymm) of a change of address.
Move Type Code
This code indicates the type of address record matched. For a list of these codes, see SSIS SmartMover Component Result Codes.
NCOA Return Code
The SmartMover component includes a new field, NCOA Return Code (for U.S records only). The NCOA Return Code indicates the level of matching between the record and the NCOA Link database. For a list of the NCOA Return Codes visit: SSIS SmartMover Component Result Codes


Output Address

Company
Map the field that will receive the Company information.
Address Line 1
Map the field that will receive the updated address or standardized street address that was used for verification. This may be the contents of the Address2 input field, if address swapping has occurred.
Address Line 2
Map the field that will receive the updated address or standardized street address that was not used for verification, if two address lines were present.
City
Map the field that will receive the city name. The information returned may be different than the contents of the City input field, if the preferred city name has been substituted for a vanity city name or if the address was coded to a different ZIP Code instead.
State/Province
Map the field that will receive the two-character state abbreviation.
Zip/Postal Code
Map the field that will receive the five-digit U.S. ZIP Code.
AddressKey
Map the field that will receive the AddressKey for the returned record. The Address Key was created by Melissa Data to uniquely identify an address and used as input to our other individual services like Geocoding.


SSIS SM OutputColumns2.png

Additional Output Columns

The Next button navigates to page 2 of the Output Columns tab. Additional Output Columns allow you to map fields for returned data that goes beyond the standard change-of-address information.

Address Information

Suite
Map the field that will receive the updated or standardized suite information, if any.
Private Mailbox
Map the field that will receive the private mailbox number. This field is populated if the primary address was identified as a Commercial Mail Receiving Agency (CMRA) and the secondary address information is present. This field would also be populated if the secondary address information contains the string "PMB," thus identifying the address as a private mailbox.
Plus 4
Map the field that will receive the four-digit extension from the ZIP + 4.
Delivery Point & Check Digit
The delivery point is a two-digit number that combined with the ZIP + 4, generates a unique number for each address in that ZIP + 4. This number is used to generate barcodes, so if you plan you use this address data for commercial mailing, it would be necessary to map an output field for this information. Typically, the Delivery Point Code will be the last two digits of the street number, but the most accurate way to obtain this information is by submitting the address for Address Verification and storing the Delivery Point code for future use.
The delivery point check digit is an additional one-digit number that is combined with the Delivery Point when generating barcodes for mailing.
Carrier Route
The Carrier Route is a four-character code that indicates which mail carrier delivers to the input address. The first character of this Carrier Route is always alphabetic and the last three characters are numeric. For example, "R001" or "C027" would be typical carrier routes. The alphabetic letter indicates the type of delivery associated with this address.
B = PO Box
C = City Delivery
G = General Delivery
H = Highway Contract
R = Rural Route
Urbanization
Map the field that will receive the Urbanization name, if any. See the entry for the Urbanization input field for more information on this field.
City Abbreviation
If the name returned by the City field is longer than 13 letters, the City Abbreviation field will return the official abbreviation the Post OfficeTM has associated with that city or municipality name. For example, the City Abbreviation field will return the abbreviation "Rcho Sta Marg" for "Rancho Santa Margarita." If the value returned by the City field is 13 letters or shorter, the City Abbreviation field will return the full city or municipality name.
Country
Returns the full name of the country in which the address is located. Currently, only Canada and the United States are supported, although SmartMover Web Service will only update the U.S. addresses.
Country Abbreviation
Returns the standard abbreviation of the country in which the address is located. Currently, only Canada and the United States are supported, although SmartMover Web Service will only update the U.S. addresses.
DPV Footnotes
JSON: Records[index].DPVFootnotes

The DPV footnotes indicate the level of matching between the current address and the USPS’s DPV database. The footnote may be up to six characters long. Refer to the table below for possible codes returned in this field.

DPV Address Status Codes

Code Description
AA Input Address Matched to the ZIP + 4 file
A1 Input Address Not Matched to the ZIP + 4 file
BB DPV matched (all components)
CC Primary Number Match – Secondary present but invalid
F1 Address Was Coded to a Military Address
G1 Address Was Coded to a General Delivery Address
M1 Primary Number missing
M3 Primary Number invalid
N1 Primary Number Match – Secondary missing
P1 Missing PO, RR or HC Box number
P3 Invalid PO, RR or HC Box number
R1 DPV matched to CMRA – PMB number not present
RR DPV matched to CMRA
U1 Address Was Coded to a Unique ZIP Code

Parsed Address Columns

These fields return street address information, split into its components.

Address Range
This field returns the street number.
Pre-directional
This field returns any directional indicators that precede the street name. The directionals are returned as one- or two-character abbreviations. If the input data contained "Southwest," it would be shortened to "SW."
Street Name
This field returns only the street name, minus the suffixes or directionals. For "SW Main Street," this would return "Main."
Suffix
This field returns the standard abbreviations for the street types: "RD," "ST," "AVE," "BLVD," etc.
Post-directional
This field returns any directional indicators that follow the street name. The directionals are returned as one- or two-character abbreviations. If the input data contained "Southwest," it would be shortened to "SW."
Suite Name
This field returns the descriptive portion of the secondary address: "SUITE," "APT," "UNIT," etc.
Suite Range
This field returns the valid Ranges for Suites for each inputted address.
Private Mailbox Name
This field returns the descriptive portion of the private mailbox number. A private mailbox, or PMB, is a secondary address associated with a Commercial Mail Receiving Agency (CMRA). Because the CMRA may itself be located in a suite, such an address may have both a suite number and a PMB number.
The name portion of the PMB will normally be either "#" or "PMB."
Private Mailbox Range
This field returns the valid Ranges for Private Mailboxes for each inputted address.
Route Service
A term used to refer to what the USPS would call a Rural Route address.
Lock Box
the Canadian equivalent of a Post Office Box in the U.S. (The two terms are often used interchangeably in Canada).
Delivery Installation
The post office facility responsible for delivering to the current address. It is often used for rural addresses or when multiple post offices service the same municipality.
Extra Information
This field returns any text from the street address field that does not fit into one of the above categories.