SSIS – Script Task to obtain Geo-Cordinates from Address Text via Google API


This post is about a quick solution to a problem which I have come across quite few times.

In few instances, I was required to obtain the “Latitude” and “Longitude” coordinates of the address stored as strings/text in the columns of the database.

This was primarily required to leverage the mapping features of the various reporting tools.

So a quick approach to resolve the above was, I leveraged the Google Geocoding API which you can read more following the link.

The main steps involved in the solution are:

  1. Using the C# script task in SSIS, for each row of data. I am invoking the google geo-code api enquiring the address information via GET request along with my specific API key.

    The URL which I fire for each row to google api is formatted as follows{Addresss I want to search}&key={Google API Key}

  2. In the above URL , for each row I am substituting the parameters highlighted above within the curly braces, and obtain the XML response from the Google geo-code API.
  3. Post which I then parse and populate the relevant transformation required output columns, with the code as listed below:
    using System.Net;
    using System.IO;
    using System.Xml;
        public override void Input0_ProcessInputRow(Input0Buffer Row)
             * Add your code here
            var xmlDoc = new XmlDocument();
            string url = "{0}&key={1}";
            var wGet = WebRequest.Create(String.Format(url, Row.SearchAddress , Variables.webApiKey));
            wGet.Method = WebRequestMethods.Http.Get;
            var response = wGet.GetResponse();
            var status = ((HttpWebResponse)response).StatusDescription;
            if (status == "OK")
                // Open the stream using a StreamReader for easy access.
                StreamReader reader = new StreamReader(response.GetResponseStream());
                // Read the content fully up to the end.
                string responseFromServer = reader.ReadToEnd();
                // Clean up the streams.
                var latNode = xmlDoc.DocumentElement.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat");
                var lngNode = xmlDoc.DocumentElement.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng");
                Row.Latitude = decimal.Parse(latNode.InnerText);
                Row.Longitude = decimal.Parse(lngNode.InnerText);



The above methods works well, but if you are looking for some long list of address data conversion, one thing to mention is that google imposes a limit on number of request one API key is allowed to make (currently it stands 2500 free request allowed, daily).

But more can be read about the same on this link Google API Usage Limits

As an add-on bonus, I have also scripted the similar logic in a stand-alone excel-vba code available in the download pack along with this post.


Download Solution Files

Hope it helps !!!

2 thoughts on “SSIS – Script Task to obtain Geo-Cordinates from Address Text via Google API”

  1. Hi, I realize this is an old post, but I’m wondering if you’ve ever encountered an issue when running this type of process in batch. When I run in batch mode, the url drops the https connection and uses http which then results in denied request from Google which requires https connection using API keys.

  2. Thank you for sharing. I would also to parse the rest of the xml elements (like \n
    political\n ). Could you please point me where I can read how I can make specific requests?

Leave a Reply

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