mSQL - SSIS Tutorial - Addressing - understanding the outputs

 

1. By default – you should see something like this your first time setting up a task if you have a standard input

 

 

Whether it passes or fails validation, we still write out the address to the result table, this way you can go straight to matching right after without having to run update queries back to your table.

That’s also why we add the _corrected suffix to all the column names, that way you can join it back to your source table and not have to worry about ambiguous column names.

 

2. Do I really need all these?  Most clients will drop urbanization if they don’t have any Puerto Rican data, if you only had one input address, then you can drop street2 and alternates likely.  But we would recommend running proper QA before committing any changes back to your source data

 

3. What if I want to parse out my street line?

lets say we have an input address of

Address1 : 4702 MILAN RD SUITE A
Address2 : NULL
Address3 : SANDUSKY         
Address4 : OHIO         
zip          : 44780



When we validate it, it becomes this, notice we appended a +4 to it, abbreviated the secondary unit indicator and state as well.
Street_corrected        : 4702 Milan Rd Ste A          
City_corrected           : Sandusky        
State_corrected         : OH      
ZipPlus4_corrected     : 44870-8911

                             

    1. if you want '4702 Milan Rd' and 'Ste a' on separate lines

      then instead of mapping street, you’d map streetonly and suite

 



StreetOnly is just '4702 Milan rd' (make sure you still map street as a source column, so we don’t lose data if an address isn’t automatically correctable)
Suite is just 'Ste A'

 

4. What if I want to Parse my zip?

 

If you wanted the zip of 44870-8911 split up, zipplus4 is where we keep it together with the - in-between.  The zip and plus4 are the separate columns, again don’t forget to map zip as a source column so you don’t lose any data by accident.

 

 

 

    1. what if I want to concatenate multiple columns?

      street2, alternates, and leftovers tend to be the ‘dumping ground’ for non-address elements when we can validate an address, by non-address elements we mean such things as attention lines, delivery instructions, duplicate addresses, and other items not relevant to the USPS, if you want us to combine them into one output column this is how you would do it.

 

Then click show advanced options:

 

Output2 will show up, and you’ll see you can choose alternates, next to street2, then output 3 will show up where you can choose leftovers. This will concatenate them in your output

 

You’ll also notice when you click advanced options that there are some hidden columns that show up.  Errorcode, and addrscore, those two columns as well as DPV can help us understand the results – such as being able to tell an invalid address from a valid one, or what changes were made, or identify possible errors with the information.  We’ll get more into the results next.

 

5. What about that filtering tab?

You can use this if you only want to work on a subset of data, such as only records from the state of NY for example, multiple filters will act like 'and' statements, if you need a more complicated filter then we suggest filling in a varchar column with a value based on your rule with SQL queries outside of the software.

 

The Advanced tab:

Processing – when presented with two different addresses, both which are valid mailing addresses, we can only choose one as the primary, then dump the second one into alternates.  You can choose if we prefer a PO Box address or a street address when presented with both.

use mixed – if you want the data in ALL CAPS, or Mixed Case

use alias – such as using an alias of Martin Luther King Blvd may be an alias for MLK Blvd, but MLK is the preferred street name by the USPS.

 

 

 

Was this article helpful?
0 out of 0 found this helpful

have a question or not finding what you're looking for?

Submit a ticket to get some help