mSQL - SSIS Tutorial - Addressing - understanding the result table

 

Query the resulting address table, If you don't see it, then right click on the database and choose to refresh, then you should be able to right click on the table that showed up if you expand the tables again and just select top 1000 records.

  

by default the same as your source but with the __addr__ suffix.  You should see something like below, there are three main things most clients concentrate on: Addrscore, DPV, and errorcode

 

the addrscore column, this represents the CASS address validation

Main items - 

0 = good
anything but 0 = failed CASS, we wouldn’t recommend updating your data with what we returned.

some examples  of the various scores – such as
4 - the best match would result in too many suspicious changes.
6 – no street address was given.
8 – The street may contain superfluous components which cannot be discarded with confidence.

get a full list of return codes here

 

Fun Facts from Wikipedia - What does CASS stand for?

Coding Accuracy Support System

The Coding Accuracy Support System (CASS) enables the United States Postal Service (USPS) to evaluate the accuracy of software that corrects and matches street addresses. 

CASS Certification must be renewed annually with the USPS to meet current CASS Certification cycle requirements.

CASS software will correct and standardize addresses. It will also add missing address information, such as ZIP codes, cities, and states to ensure the address is complete. CASS software will also perform delivery point validation to verify whether or not an address is a deliverable address and check against the USPS Locatable Address Conversion System (LACS) to update addresses that have been renamed or renumbered.

A correct address saves the Postal Service time, money and manpower by reducing the volume of 1) non-deliverable mail; 2) unsorted mail; 3) mail that is deliverable, but requires extra effort to determine the proper location to which it should be delivered.

 

DPV_corrected column, DPV is the second level of validation, CASS is the first

There are 4 main return items

Starts with Y – valid for all delivery points – show an example of an address both with and without a street

Starts with an S = means that an apt/suite is there, but not what the USPS expects

Starts with D, means that there is possibly missing secondary apt/suite information

Starts with an N – means that it's only valid down to the street, the house number is not deliverable, it's just within an expected range

 

the Errorcode column – this indicates changes we made to the input.

such as this input

Address1: 23 State Route 339 S     
Address2:        
Address3:  Fancie Farm

 

Turned into this:

Street_corrected: 23 State Route 339 S 
Street2_corrected:  
Urbanization_corrected:  
City_corrected: Fancy Farm
State_corrected: KY   
ZipPlus4_corrected:  42039-9998

It has an errorcode of   APQRT
meaning these changes were made:
A – normal street match
P – City corrected
Q – State corrected
R – Zip corrected
T – Zip+4 corrected