October 2013 – The Data Queen

I’ve had to do some geocoding of addresses. It used to be free to process a lot of addresses, but now that this is a more mainstream activity you generally have to purchase an Enterprise License. This can cost upwards of $10,000. If you have a very small number of addresses you can use a free service like Bing Maps, which currently allows you to process 50 records at a time, and run 5 jobs per 24 hours. This means a maximum of 250 per day. If you want to check out how this works, here’s how.

Install the free Codeplex SSIS Batch Geocoder into your Visual Studio environment

http://ssisbatchgeocoder.codeplex.com/releases/view/66866

Get a Bing Maps Key

You need to have or set up a Microsoft account to get a key. Follow the instructions to get a Basic Key. If you have a large number of addresses you will want to look into purchasing an Enterprise License.

http://www.microsoft.com/maps/

Set up an SSIS project

Create a Visual Studio SSIS project. If you can’t see the toolbox, in the Visual Studio Menu choose View – Other Windows – SSIS Toolbox. If you can’t see the Variables window, in the Visual Studio Menu choose View – Other Windows – Variables.

Add these two string variables to your project: BingMapsKey and JobDescription. Set the value of the BingMapsKey variable to the key you obtain from Bing Maps. Set the JobDescription variable to “Geolookup from address”

Add a Data Flow Task. Open up the Data Flow Task. On the Data Flow Task add these four items:

Configure the OLE DB Source to connect to the table that contains the addresses.

Configure the Derived Columns like this:

Configure the SSIS Batch Geocoder like this, using the two variables as the Bing Maps Key and the Job Description, and mapping any other relevant columns from your data.

Configure the OLE DB Destination to wherever you want your output to reside. Choose whichever output fields meet your needs. The latitude and longitude output can be stored in a field of SQL data type “Geography”.

In preparing this post I found these articles helpful.

http://ssisbatchgeocoder.codeplex.com/documentation

http://blog.programmableweb.com/2012/06/21/7-free-geocoding-apis-google-bing-yahoo-and-mapquest/

I’ve seen this issue a lot lately. There is a need to feed a comma delimited string of values into a multi-value parameter in an SSRS report. There is a simple way to do this.

SET UP YOUR MAIN REPORT QUERY

Your main report query should be set up to expect a string of values in the parameter. For example:

SELECT Product_ID
      ,Item_No
      ,Item_Description
  FROM DIM_Product
  WHERE Item_No IN (@ITEM)

SET UP THE PARAMETER

The parameter should be set up as text, but NOT allow multiple values.

You can set this up as you wish to meet your purpose, but for demonstration I will set up two groups of comma delimited strings as available values.

I have set up a second data set to feed these default values to the parameter.

SELECT '11000B,2200,17000' AS ITEMNMBR

SET UP THE PARAMETER ON THE MAIN REPORT DATASET

Now comes the magic. On the Dataset Properties for your main report dataset, on the Parameters tab, edit the expression for the Parameter Value.

Write this in the parameter expression: =split(Parameters!Item.Value,",")

This will take the comma delimited string as input, split it into individual values and it will get used in the IN clause of the main query that we set up at the beginning of this post.

WHERE Item_No IN (@ITEM)

Viola, the report filters on a string of values.

Month: October 2013

Batch GeoCoding in SSIS