Overview

<div class="wikidoc"><h1>Overview</h1>The MapPoint Batch Geocoder is an open source transformation component for SQL Server Integration Services that performs batch geocoding of address information directly within the SSIS pipeline using the geocoding capabilities of MapPoint&#39;s Customer Data Services.  The transformation outputs both the original input data and the geodetic spatial data using the new Spatial Data Types of SQL Server 2008.<br /><br />The transformation leverages the geocoding capabilities of MapPoint&#39;s Customer Data Services.  Address information within the pipeline is buffered and then uploaded to MapPoint CDS in batches.  Once MapPoint geocodes the addresses, the data is downloaded and reintegrated back into the SSIS pipeline.  During reintegration, spatial data is joined back with the original input data and continued through the pipeline.  <br /><br />The output columns include both the original input columns as well as two additional columns: 
<ul><li><b>GeoPoint</b> - Geocoded address as a SqlGeography spatial data type.</li>
<li><b>MatchCode</b> - The geocoding accuracy MapPoint associates with each address.</li></ul>
<h1>Capabilities</h1><ul><li>Geocodes addresses directly in the SSIS pipeline.</li>
<li>Performs geocoding in batches as opposed to row-by-row.</li>
<li>Leverages the geocoding capabilities of MapPoint&#39;s Customer Data Services.</li>
<li>Outputs the SQL Server 2008 geodetic (vs. geometry) spatial data type.</li>
<li>Ability to select which input columns contain address information (AddressLine, PostalCode, etc).</li>
<li>Variable based configuration of MapPoint settings to allow environment specific configuration.</li>
<li>The ability to select between the staging or production environments of MapPoint&#39;s CDS.</li>
<li>Pass-thru of additional input columns of data (non address data) in the output pipeline.</li>
<li>Allows stubbing of the geocoding operation (generation of random values for lat/lon instead of invoking the MapPoint web services) for disconnected testing scenarios.</li>
<li>Web Service retry capability in case of minor network/Internet failures, for added resiliency.</li></ul>
<h1>Prerequisites</h1><ul><li>SQL Server 2008 Integration Services</li>
<li>Visual Studio 2008 C# (for code modification and compilation)</li>
<li>.NET Framework 3.5</li></ul>
<h1>Installation</h1><ol><li>Compile the MapPoint Batch Geocoder transformation component. </li>
<li>Copy the transformation component (MapPoint Batch Geocoder.dll) to the PipelineComponents folder for SSIS (i.e. &quot;C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\&quot;).</li>
<li>Add the assembly to the Global Assembly Cache (GAC). (see article &#39;How to: Install an Assembly into the Global Assembly Cache&#39; - <a href="http://msdn.microsoft.com/en-us/library/dkkx7f79.aspx">http://msdn.microsoft.com/en-us/library/dkkx7f79.aspx</a>)</li>
<li>On the Data Flow tab of SSIS, right click in the toolbox and select &quot;Choose Items…&quot;.</li>
<li>Select the &quot;SSIS Data Flow Items&quot; tab.</li>
<li>Choose &quot;MapPoint Batch Geocoder&quot; from the list of components, and click OK.</li></ol>
The component should now be available from the list of Data Flow Transformations in the toolbox.  For convenience, an installation package is also available which installs the runtime version.  However, it does not add the component to Visual Studio.
<h1>Usage</h1>The MapPoint Batch Geocoder works just like any other transformation component; once you add a Data Flow task to the Control flow design surgace, drag and drop the &quot;MapPoint Batch Geocoder&quot; from the toolbox and place it somewhere between a source and destination.  After connecting the transformation to upstream (input) item and selecting the available input columns, configuration of the geocoder can begin.
<h2>Transformation Configuration</h2>Below is a list of the available configuration options:
<ul><li><b>MapPoint UserName</b> - The user name of the MapPoint account to use for geocoding.</li>
<li><b>MapPoint Password</b> - The password of the MapPoint account to use for geocoding.</li>
<li><b>MapPoint DataSourceName</b> - A fully qualified target data source name used when uploading data to MapPoint.  All address data uploaded to MapPoint must be stored in a data source.  Typically this data source is used to store address information by subject area and then referenced when rendering a map.  For our purposes, we will use this data source temporarily for the life of the package instance, and discard once the geocoding completes.  The data sources must be uniquely named to avoid collisions during processing when multiple instances of the batch geocoder execute simultaneously (i.e. MyAccount123456.123456.Offices and MyAccount123456.123456.Customers).</li>
<li><b>MapPoint UseStaging</b> - Set to true to perform the geocoding in MapPoint&#39;s staging environment.</li>
<li><b>Source AddressLine</b> - The input column to use as the AddressLine as required by MapPoint.</li>
<li><b>Source EntityID</b> - The input column to use as the EntityIDas required by MapPoint.</li>
<li><b>Source PostalCode</b> - The input column to use as the PostalCode as required by MapPoint.</li>
<li><b>Source PrimaryCity</b> - The input column to use as the PrimaryCity as required by MapPoint.</li>
<li><b>Service Stub MapPoint Data</b> - If true, will cause the transformation to avoid any Web Service calls and synthesis fake lat/lon values instead of invoking the service. Useful for testing purposes.</li>
<li><b>Service Retry Attempts</b> - Number of attempts to retry accessing MapPoint services in case of network failure.</li>
<li><b>Service Retry Delay</b> - The delay between retries (in seconds).</li></ul>
<h2>Logging</h2>Log events are generated during geocoding and can be captured using standard SSIS logging.  All events are reported under the OnInformation event.
<h1>Design Considerations</h1><h2>Isolating the Geocoding Process</h2>The possibility of connectivity failure increases anytime Internet-based services are involved, as is the case with this component’s dependency on the MapPoint’s CDS webservice.  The ETL processes can be designed so that a geocoding failure does not cause the entire ETL process to fail, particularly if the ETL process is long and difficult/tedious to restart.<br /><br />Instead of performing geocoding as an extra data flow step in an already complex package, a more resilient design is to have a separate package which performs the geocoding against records which have already been processed by ETL and landed in the target database.  The package will only geocode records which have yet to be geocoded, indicated by a null or flag.  Once the geocoding is complete, the package updates the existing rows and clears the flag.<br /><br />Because of the design, the geocoding package can be executed out-of-band, which handles the case of a network failure or even a historical reload of data.
<h2>Data Cleansing</h2>Due to the nature of how address information is entered by operational systems, it is quite common for this information to contain errors (formatting, spelling, typos, etc).  However, the MapPoint Batch Geocoder will not perform any data cleansing of address information before submitting to MapPoint.  Since error-prone address information will yield poor or incorrect geocoding results, consideration for adding data cleansing practices to ETL processes should be made.<br /><br />The MatchCode value returned by MapPoint can be used to identify records that had an issue being geocoded.  A decision should be made for each possible MatchCode value of whether the quality of the geocode is useful enough to be consumed by downstream systems and whether further address cleansing (i.e. manual intervention) is required.
<h2>Controlling the MapPoint Batch Size</h2>For performance reasons, geocoding is performed in batch mode.  The batch size is controlled by the SSIS buffer controls DefaultBufferMaxRows and DefaultBufferSize properties of a data flow task.  Managing the batch size is useful for achieving the right balance between geocoding too much data at once (i.e. all is lost in case of failure, or MapPoint limitations), and overall process performance.
<h2>MapPoint Limitations</h2>Because the MapPoint Batch Geocoder is based on the MapPoint CDS webservice, it must adhere to the rules and constraints dictated by the Customer Data Services.  Although most of these restrictions are already accounted for in the transformation’s design, some may still require consideration.  For instance:
<ul><li>The combined size of all your data sources cannot be more than 2 gigabytes after being uploaded and uncompressed.</li>
<li>Any single file for uploading cannot be more than 100 megabytes (compression is allowed).</li></ul>
See Formatting MapPoint Web Service Data Source Files for more information - <a href="http://msdn.microsoft.com/en-us/library/cc534849.aspx">http://msdn.microsoft.com/en-us/library/cc534849.aspx</a></div><div class="ClearBoth"></div>