Working with Spatial Data Part I – Geocoding Text-Based Spatial Data for Use In SSIS Packages
SSIS Package Development:
Once you have completed the environment setup as instructed above. To create the SSIS package:
1) Create a new SSIS project using Business Intelligence Development Studio (BIDS) and name it “BI-Satellite”.
2) Create an OLE DB connection to the database of our “Address” table.
3) Add a Data Flow task to the package, edit it and add an OLE DB Source Adapter, name this “Address”. and configure it to use the connection we created in step-2 and read data from the Address table.
4) Create two derived columns of the string data type using derived columns transformation and name them latitude and longitude. Assign blank values to these fields, use the “Advanced Editor” and edit the length of these fields to a considerably large size. I assigned the size of “100” although a length of 20 should be sufficient to accomodate latitude or longitude values. You might be wondering if we should be using decimal or any numeric data-types, but there are CLR based functions which can be used with latitudes and longitudes as inputs in string format, so we need keep these as strings for now.
5) Add a Script transformation and name it “Geocode”, select the type as “Transformation” and Scripting language as Visual C#. Edit the latitude and longitude columns and make them writable, as shown in the below screenshot. Edit the script component, by right-clicking in the project explorer and selecting “Add existing item”. Select the proxy class “Geocode.cs” that we created earlier and then build it from the Build menu.
Add references to the System.Web.Services and System.Xml assemblies (do this by right-clicking the project explorer and selecting “Add Reference”). We now need to reference these assemblies to use our proxy class.
6) Now add the code as you can see from the below snippet. I have used modified code from the Bing Maps sample application for .NET that ships with Microsoft samples, as the sample code adds web-references to the project which creates a client and is not exactly the same as adding reference in the Scripting environment of Script transformation. Accessing a web-service by creating a proxy is one of the most standard ways of accessing a web-service from SSIS Script transformation in my view. Build this code, and if all the steps are followed correctly, the build should succeed.
In the GeocodeAddress function, we are creating a GeocodeRequest object and passing the key which we created in the BingMaps portal. Next, we set the filter to return only results with the best precision since one request can result in multiple possible results if the textual location information is not precise. Then we create an object of the GeocodeService class, and make a call to the Geocode function, passing GeocodeRequest object as the parameter. We collect results of the this in GeocodeResponse object and read the values of the latitude and longitude properties.
Finally, call is made to the “GeocodeAddress” function for each row and sets values of Latitude and Longitude fields from the values retrieved from this function.
public class ScriptMain : UserComponent
public override void PreExecute()
Add your code here for preprocessing or remove if not needed
public override void PostExecute()
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
public override void Input0_ProcessInputRow(Input0B
Add your code here
string latitude = “”, longitude = “”;
, ref latitude, ref longitude);
Row.Latitude = latitude;
Row.Longitude = longitude;
private void GeocodeAddress(string address, ref string latitude, ref string longitude)
= new GeocodeRequest();
// Set the credentials using a valid Bing Maps key
geocodeRequest.Credentials = new Credentials();
// Set the full address query
geocodeRequest.Query = address;
// Set the options to only return high confidence results
ConfidenceFilter filters = new ConfidenceFilter;
filters = new ConfidenceFilter();
filters.MinimumConfidence = Confidence.High;
// Add the filters to the options
GeocodeOptions geocodeOptions = new GeocodeOptions();
geocodeOptions.Filters = filters;
geocodeRequest.Options = geocodeOptions;
// Make the geocode request
GeocodeService geocodeService = new GeocodeService();
latitude = geocodeResponse.Results.
longitude = geocodeResponse.Results.
7) Returning back to the package, now we need to add a destination. We just need to view to geocoded values, so simply add a Multicast transformation which can act as a destination for our package.