Wednesday, June 19, 2013

Update of Latitude/Longitude fields using BingMaps services through SSIS Integration Toolkit

During current project I have got task to update Latitude/Longitude fields of all account records based on their address. I’ve done similar tasks several times but this time I decided to create SSIS package that could be reused in future projects. So in this article I will share with my results.
There are 3 prerequisites for this article:
1. You need workstation where BIDS is installed.
2. You need SSIS Integration Toolkit installed.
3. You need valid BingMap key.
Once this is done open BIDS and create new Integration Services Project, Create new Data Flow and add CRM Source into it, configure connection to your CRM instance and use following FetchXml as a source for your data:
<fetch version="1.0" output-format="xml-platform" mapping="logical">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="address1_line1" />
    <attribute name="address1_country" />
    <attribute name="address1_city" />
    <attribute name="address1_stateorprovince" />
    <attribute name="address1_postalcode" />
    <filter type="and">
      <condition attribute="address1_city" operator="not-null" />
      <condition attribute="address1_line1" operator="not-null" />
    </filter>
  </entity>
</fetch>


Click OK and add new Script Component to your flow, choose “Transformation” option in dialogue that will appear:


Connect output of CRM Source to Script component input:

Mark all input columns to be passed to component:

Open Script Component and add 2 output columns of decimal type – longitude and latitude and Resolved column of boolean type:



Once this is done return back to Script tab and click “Edit Script” button:

Add a reference to System.Web assembly.
Add following code to Input0_ProcessInputRow method:

Row.Resolved = false;

//Put your valid Bing Map Key here
string bingmapkey = "Your secret key";

//Here I concatenate all address fields into one
string fulladdress = Row.address1line1_IsNull ? string.Empty : Row.address1line1;
fulladdress += (string.IsNullOrEmpty(fulladdress) ? string.Empty : " ") +
    (Row.address1city_IsNull ? string.Empty : Row.address1city);
fulladdress += (string.IsNullOrEmpty(fulladdress) ? string.Empty : " ") +
    (Row.address1stateorprovince_IsNull ? string.Empty : Row.address1stateorprovince);
fulladdress += (string.IsNullOrEmpty(fulladdress) ? string.Empty : " ") +
    (Row.address1country_IsNull ? string.Empty : Row.address1country);

//Here I create an url that would be used for geolocation
string bingmapRequestUrl = "http://dev.virtualearth.net/REST/v1/Locations?q=" +
    System.Web.HttpUtility.UrlEncode(fulladdress) + "&key=" + bingmapkey + "&o=xml";

//Here I load results of request
XmlDocument doc = new XmlDocument();
doc.Load(bingmapRequestUrl);

XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("rest", "http://schemas.microsoft.com/search/local/ws/rest/v1");

//Parsing of response
XmlNode statuscode = doc.SelectSingleNode("//rest:StatusCode", nsmgr);

if (statuscode == null || statuscode.InnerText != "200")
    return;

XmlNode location = doc.SelectSingleNode("//rest:ResourceSets/rest:ResourceSet/rest:Resources/rest:Location/rest:Point", nsmgr);

System.Globalization.CultureInfo culture = System.Globalization.CultureInfo.InvariantCulture;

if (location != null)
{
    //In case address was resolved I put Longitude and Latitude to output
    Row.Longitude = decimal.Parse(location.SelectSingleNode("./rest:Longitude", nsmgr).InnerText, culture);
    Row.Latitude = decimal.Parse(location.SelectSingleNode("./rest:Latitude", nsmgr).InnerText, culture);
    Row.Resolved = true;
}

Save and close results.

Add Conditional Split component to Data Flow and connect output of Script Component to Input of Conditional Split component:

Open Conditional Split Component and add split condition as it shown:

Add CRM Destination component to Data Flow and connect Conditional Split Component output “Resolved Addresses” to CRM Destination input:

Configure CRM Destination component as it shown:

Run package: