Using the SSIS Scripting Task to Geocode Addresses

In February I was lucky enough to see Brian Larson give a talk on GeoSpatial visualization using SSRS at the PASSMN meeting. He showed us how to use the Map Wizard in SSRS to quickly create some cool reports. As he was looking up the latitude and longitude for the addresses I remembered a project I had worked on using SSIS and Google Maps to geocode data. For the March PASSMN Meeting I agreed to share some information on the Scripting Task in SSIS and I decided to use the geocode example. Since I was giving this presentation in a Microsoft building I changed from Google to Bing.  Leave out having to learn the Bing API in a couple of days the package for doing this is pretty straight forward. A SQL Task to get the address information is passed to a For Each Loop that runs the script for each address. The Loop updates the Lat and Long and then a final SQL task to create the geospatial points in the database. Continue reading