In the current project I’m working on, I have to update Latitude/Longitude fields of all account records based on their addresses. I’ve done similar tasks several times before, but this time I decided to create an SSIS package that could be reused in future projects. So in this article I will share with my results with you.

There are 3 prerequisites for this article:

1. You need a workstation where BIDS is installed.
2. You need an SSIS Integration Toolkit installed.
3. You need a valid BingMap key.
Once this is done, open BIDS and create a new Integration Services Project, Create new Data Flow and add the CRM Source into it, configure the connection to your CRM instance and then 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 the new Script Component to your flow, then choose the “Transformation” option in the dialogue box that will appear:

 

Connect the output of CRM Source to the 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 it back to Script tab and click “Edit Script” button:

Add a reference to the System.Web assembly.
Add the 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 the results.

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

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

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

Configure the CRM Destination component as it shown:

Run the package: