Help Manual - Address Verification

Documentation on SSIS Address Verification component

Using the Address Verification Component

The Address Verification Component is an SSIS data flow pipeline component that can be used to verify the accuracy of address data and provide corrections or add missing data for addresses that are not accurate.

The component uses 3rd party address verification services to verify addresses. In most cases, a paid subscription is required to use one of these services which are not included with our software. There are currently two services supported:

It is important to note that the way the two services work is vastly different, therefore the results returned by the two services are different. SmartyStreets returns the number of matched candidates, which can be used to determine whether the received address is a valid one, and in addition, the verified address is provided through the Validation Candidates output. In other words, the Primary Output (or the main output) does not contain the completed result. In contrast, EasyPost works by verifying whether a provided address can be used for delivery which, in most cases, can be translated to whether the address is good for shipping. Consequently, the results returned by EasyPost would indicate whether the received address is valid for delivery, in addition, it would return the completed address in its Primary Output.

The component includes four pages to configure how you want your address verification to work.

General Page

The General page allows general properties to be set for the component. Properties are specific to the verification provider that was selected in the connection manager.

EasyPost Properties
SSIS Address Vertification

There are no additional property settings to configure when an EasyPost Connection Manager is used.

Smarty Streets Properties
SSIS Address Vertification
US Address Verification checkbox
Determines if the address to be verified is US or International.
Batch Size
The number of addresses to send per address verification web request. This option is only available for US Address Verification.
Number of Candidates
The number of candidates to return in the event that there are multiple matches to an address verification. This option is only available for US Address Verification.
Match Strategy
The match strategy to use when verifying addresses. This option is only available to US Address Verification. The available options are:
Strict: The API will ONLY return candidates that are valid USPS addresses.
Range: The API will return candidates that are valid USPS addresses, as well as invalid addresses with primary numbers that fall within a valid range for the street.
Invalid: The API will return a single candidate for every properly submitted address, even if invalid or ambiguous.
Enhanced: The API will return detailed output based on a more aggressive matching mechanism. It also includes a more comprehensive address dataset beyond just the postal address data. 
Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each attribute to its most recent metadata.

Map Unmapped Fields Button

By clicking this button, the component will try to map any unmapped attributes by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped destination attributes.

Clear All Mappings Button

By clicking this button, the component will reset all your mappings.

Expression fx Button

Clicking the fx button to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Button

Clicking the Generate Documentation button to generate a Word document that describes the component's metadata including relevant mapping, and so on.

Input Page

The address verification input configuration page allows you to map SSIS input columns to available Address fields in the address verification service provider that has been selected.

SSIS Address Vertification - Input Configuration

  • Input Column: You can select an input column from an upstream component for the corresponding Address Field.
  • Address Field: The field that you are writing data to.
  • Data Type: This column indicates the type of value for the current field.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.
  • Parent ID Column: The parent ID column combo box allows an input column to be selected that will have its value output to the _parentId column of the secondary output. This will allow results from the secondary output to be linked to the primary output. For example, if a SmartyStreets request returns multiple candidates per input row the _parentId value would exist in both primary and secondary output and allow those results to be linked together. The default option for the Parent ID Column is _rowIndex which will create an additional _rowIndex column in the primary output, and use that value in the _parentId column in the secondary output.

Outputs Page

The address verification component has 2 outputs and an error output. The primary output will have 1 output row per input row. The secondary output will have 0 or more rows per input row and can represent things like a list of errors, or a list of candidate addresses depending on the service.

SSIS Address Vertification - Outputs

On the top left of the grid, the checkbox can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

If the Show Passthrough Columns checkbox is checked all of the input columns will be shown in the primary output grid. These columns can not be toggled off and are available only to give a better idea of what the primary output will look like.

The Columns Page grid consists of:

  • Field Name: Column that will be retrieved from Google Sheets.
  • Data Type: The data type of this field.

Note: EasyPost address rows that are verified successfully will return a verified_id value. This ID can be used to retrieve a shipping label from EasyPost although that is outside the scope of the address verification component.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

SSIS Address Vertification - Error Handling

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to write to the Address Verification will be redirected to the ‘Error Output’ output of the Destination Component. As indicated in the screenshot below, the green output connection represents rows that were successfully written, and the red ‘Error Output’ connection represents erroneous rows. The ‘ErrorMessage’ output column found in the ‘Error Output’ may contain the error message that was reported by the server or the component itself.

SSIS Address Vertification - Error Output

Address Verification and Outputs

After the execution of the component, the validation results will be sent to the component's outputs based on what was returned by the service used.

  • SmartyStreet
    When using SmartyStreets address verification service in the component, the service will verify whether each address is valid and return candidates' addresses. There are two outputs available:
Output Name Description
Primary

The Primary output includes all input columns which contain the same values passed to the component, plus one additional column:

  • candidateCount: This column contains the number of valid addresses that have been found matching with the input address. When the value is 0, it generally means that no valid match can be found, which usually indicates that the received address is not valid, or does not exist at all.
Validation Candidates The details of the matched valid addresses for the input data. It is important to note that SmartyStreets might return more than one matched row for each received address. It would be up to the ETL developer to decide which address can be taken to complete the original input.
  • EasyPost
    When EasyPost address verification service is used, it will check whether the address is deliverable and also report any errors during the validation. There are also two outputs available:
Output Name Description
Primary

The Primary output contains the verification results and detailed valid address information if verification succeeded. Pay attention to the following columns:

  • verifications.delivery.success: Returns True/False value to indicate whether the received address is valid for delivery (shipping) or not. When true, the other fields in the same output may contain completed information if the input is incomplete. For instance, if an input address does not have Zip Code. EasyPost might complete the address in which case the Zip Code column can contain the returned zip code as provided by EasyPost.
  • errorCount: The number of errors for the input address verification.
Validation Errors The details of the errors for the input address verification.