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.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.
using Microsoft.SqlServer.Dts.
[Microsoft.SqlServer.Dts.
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.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 = “”;
GeocodeAddress(Row.
Row.AddressLine2 + “, ” +
Row.City
, ref latitude, ref longitude);
Row.Latitude = latitude;
Row.Longitude = longitude;
}
private void GeocodeAddress(string address, ref string latitude, ref string longitude)
{
string key
= @”****************************
GeocodeRequest geocodeRequest
= new GeocodeRequest();
// Set the credentials using a
valid Bing Maps key
geocodeRequest.Credentials = new Credentials();
geocodeRequest.Credentials.
//geocodeRequest.Credentials.
= key;
// Set the full address query
geocodeRequest.Query = address;
// Set the options to only
return high confidence results
ConfidenceFilter[] filters = new ConfidenceFilter[1];
filters[0] = new ConfidenceFilter();
filters[0].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();
GeocodeResponse geocodeResponse
= geocodeService.Geocode(
if (geocodeResponse.Results.
> 0)
{
latitude = geocodeResponse.Results[0].
longitude = geocodeResponse.Results[0].
}
}
}
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.