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 is not included with our software. There are currently 2 services supported:

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

General Configuration

The address verification 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.

SSIS Address Vertification

EasyPost Properties

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

Smarty Streets Properties

US Address Verification checkbox

Determines if the address to be verified are US or International

Batch Size

The number of addresses to send per address verification web request. This option is only available to 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 to 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.

Functional Buttons

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 update of the property at run time.

Generate Documentation Button 

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

Input Configuration

The address verification input configuration page allows you to map SSIS input columns to available input fields in the address verification 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 return 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 primary output, and use that value in the _parentId column in secondary output.

Outputs Configuration

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 - Output

On the top left of the grid, you can see a checkbox, which 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 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 Configuration

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

SSIS Address Vertification - Error Output

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 blue output connection represents rows that were successfully written, and the red ‘Error Output’ connection represents rows that were erroneous. The ‘ErrorMessage’ output column found in the ‘Error Output’ may contain the error message that was reported by server or the component itself.

SSIS Address Vertification - Error Output Flow