Development

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

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:

 

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:

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:

Leave a Reply

Your email address will not be published. Required fields are marked *