Address Verification

Address Verification

Parent Previous Next


Address Verification Transform


The "Address Verification Transform" uses the USPS database to verify United States addresses.


Important Notes

Address Verification Transform is available for SQL versions 2012 and higher.

Address Verification Transform must be run in 32-bit mode.

An internet connection is required to update the Address Verification database.


Source Data



 Firm Name Source Column - (Optional) Maps the column that contains the person or firm's name.

 Address1 Source Column - (Required) Maps the column that contains the first address line.

 Address2 Data Column - (Optional) Maps the column that contains the second address line (e.g. suite or apartment numbers, building name, etc.)

 City Source Column - (Required) Maps the column that contains the city.

 State Source Column - (Required) Maps the column that contains the state.

 Zip Code Source Column - (Required) Maps the column that contains the zip code.

 Unverified Address Behavior - Options for handling unverified addresses

o  Ignore unverified (unverified addresses will be skipped) - Skips the unverified address and continues execution.

o  Redirect unverified to unverified output - Redirects all unverified addresses to a designated unverified output.

o  Fail upon first unverified address found - Fails the component as soon as an unverified address is encountered.


Output Columns



 Address Data Column - Identifies the column

 Column Behavior - Users can choose between the following:

o  Replace Column

o  Add New Column

o  Do Not Output Column

Output Column Name - Users can change the name of the output column


For more information about the component's output, please see our Guide to Understanding the USPS Address Verification Output

The following list details the Address Verification Transform's output:


Column Name

Data Type

Length

AddressLine1

DT_WSTR

255

AddressLine2

DT_WSTR

255

City

DT_WSTR

255

State

DT_WSTR

3

ZipCode

DT_WSTR

10

ResponseCode

DT_I1

0

AbbreviatedCity

DT_WSTR

255

AddressComponentChanged

DT_BOOL

0

AddressKey

DT_WSTR

255

AddressNotFound

DT_BOOL

0

AddressStandardized

DT_BOOL

0

AllOrPartFirmLineUsedForAddressLine

DT_BOOL

0

BetterAddressExists

DT_BOOL

0

CarrierRoute

DT_WSTR

255

CarrierRouteRateSortIndicator

DT_WSTR

255

CityStateCorrected

DT_BOOL

0

CountyCode

DT_WSTR

255

DeliveryPoint

DT_WSTR

255

FirmName

DT_WSTR

255

FirstPostDirection

DT_WSTR

255

FirstPreDirection

DT_WSTR

255

FirstSuffix

DT_WSTR

255

IncorrectSecondaryNumber

DT_BOOL

0

InsufficientIncorrectData

DT_BOOL

0

InvalidCityStateZip

DT_BOOL

0

LookupStatus

DT_WSTR

255

MainPostOfficeOutputCity

DT_WSTR

255

MainPostOfficeOutputState

DT_WSTR

255

MatchedPrimaryNumber

DT_WSTR

255

MatchedSecondaryNumber

DT_WSTR

255

MilitaryMatch

DT_BOOL

0

MissingSecondaryNumber

DT_BOOL

0

MultipleResponseCanBeBrokenUsingTheLowestZip4

DT_BOOL

0

MultipleResponseDueToMagnetStreetSyndrome

DT_BOOL

0

NoMatchDueToEws

DT_BOOL

0

NonPoBoxDualAddress

DT_BOOL

0

NoZip4Assigned

DT_BOOL

0

PoBoxDualAddress

DT_BOOL

0

PrimaryName

DT_WSTR

255

PrimaryNumber

DT_WSTR

255

RuralRouteNumber

DT_WSTR

255

SecondaryNumber

DT_WSTR

255

SecondaryNumberUnit

DT_WSTR

255

SecondPostDirection

DT_WSTR

255

SecondPreDirection

DT_WSTR

255

SecondSuffix

DT_WSTR

255

SmallTownDefault

DT_BOOL

0

StandardizedOutputCityStateZip

DT_WSTR

255

StandardizedPrUrbanizationName

DT_WSTR

255

StreetNameChanged

DT_BOOL

0

UniqueZipCodeDefault

DT_BOOL

0

UniqueZipCodeMatch

DT_BOOL

0

UnofficialPostOfficeName

DT_BOOL

0

UnverifiableCityState

DT_BOOL

0

ZipCode4

DT_WSTR

255

ZipCodeAssignedWithMultipleResponse

DT_WSTR

255

ZipCodeCorrected

DT_BOOL

0

ZipMoveMatch

DT_BOOL

0

Videos

https://pragmaticworks.wistia.com/medias/d6x562en2s