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>
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; }
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:
No comments:
Post a Comment