Geocode Transformation

Library references

• System
• System.Data
• Microsoft.SqlServer.TxScript
• Microsoft.SqlServer.PipelineHost
• Microsoft.SqlServer.DTSPipelineWrap
• Microsoft.SqlServer.DTSRuntimeWrap
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus
• System.Web

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2009 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. ' Following resources were used as reference: ' http://code.google.com/apis/maps/documentation/geocoding/index.html ' http://code.google.com/apis/maps/articles/phpsqlgeocode.html Imports System Imports System.ComponentModel Imports System.Collections Imports System.Net Imports System.Web Imports System.IO Imports System.Threading Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"AddressColumn", "ApiKeyVariable", "CacheFile"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent Private Const Query_2P As String = "http://maps.google.com/maps/geo?output=csv&oe=utf8&sensor=false&key={0}&q={1}" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Dim address As String = CStr(Row.Buffer(m_addressIndex)) ' Geocode address. Dim geo() As Object = GeoCode_(address) Row.Accuracy = CInt(geo(0)) Row.Latitude = CDbl(geo(1)) Row.Longitude = CDbl(geo(2)) End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Setup address index. Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) m_addressIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.AddressColumn).LineageID) ' Setup API key. Dim vars As IDTSVariables90 Call Me.VariableDispenser.LockOneForRead(Me.ApiKeyVariable, vars) Try m_apiKey = CStr(vars(Me.ApiKeyVariable).Value) Finally Call vars.Unlock() End Try ' Setup cache. m_cache = LoadCache_() m_cacheModified = False End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() If m_cacheModified Then ' Cache has been modified. Store it. Call StoreCache_(m_cache) End If End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub ReinitializeMetaData() Call MyBase.ReinitializeMetaData() ' Setup address column. Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput() Call input.InputColumnCollection.RemoveAll() Call virtInput.SetUsageType( _ virtInput.VirtualInputColumnCollection(Me.AddressColumn).LineageID, _ DTSUsageType.UT_READONLY) ' Setup cache file connection manager. Me.ComponentMetaData.RuntimeConnectionCollection("CacheFile").ConnectionManagerID = Me.CacheFile End Sub ' ReinitializeMetaData ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.AddressColumn) Then Throw New ApplicationException("Select address column.") End If If String.IsNullOrEmpty(Me.ApiKeyVariable) Then Throw New ApplicationException("Select API key variable.") End If If String.IsNullOrEmpty(Me.CacheFile) Then Throw New ArgumentException("Select cache file connection manager.") End If Call ReinitializeMetaData() result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select address column.")> _ <InputVirtualColumn()> _ Public Property AddressColumn() As String Get AddressColumn = m_addressColumn End Get Set(ByVal value As String) m_addressColumn = value End Set End Property ' AddressColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select file containing geocoding cache.")> _ <Connection("FileConnectionType", True)> _ Public Property CacheFile() As String Get CacheFile = m_cacheFile End Get Set(ByVal value As String) m_cacheFile = value End Set End Property ' CacheFile ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select variable containing Google Maps API key.")> _ <Variable()> _ Public Property ApiKeyVariable() As String Get ApiKeyVariable = m_apiKeyVariable End Get Set(ByVal value As String) m_apiKeyVariable = value End Set End Property ' ApiKeyVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property FileConnectionType() As String() Get FileConnectionType = New String() {"FILE"} End Get End Property ' FileConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Loads current geocode cache file. Private Function LoadCache_() As Hashtable Dim result As Hashtable = New Hashtable() Dim fileName As String fileName = Me.Connections.CacheFile.ConnectionString If File.Exists(fileName) Then ' Cache file exists. Load it. Using reader As StreamReader = New StreamReader(fileName) While Not reader.EndOfStream Dim line() As String = reader.ReadLine().Split(Chr(124)) result.Add(line(0), New Object() {CInt(line(1)), CDbl(line(2)), CDbl(line(3))}) End While End Using Dim fireAgain As Boolean Me.ComponentMetaData.FireInformation( _ 0, _ "Geocode", _ String.Format("Loaded {0} cached addresses.", result.Count), _ String.Empty, _ 0, _ fireAgain) End If LoadCache_ = result End Function ' LoadCache_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Stores current geocode cache. Private Sub StoreCache_(ByVal cache As Hashtable) Dim fileName As String = Me.Connections.CacheFile.ConnectionString Using writer As StreamWriter = File.CreateText(fileName) For Each pair As DictionaryEntry In cache Dim line As String = CStr(pair.Key) For Each item As Object In CType(pair.Value, Object()) line += Chr(124) + item.ToString() Next Call writer.WriteLine(line) Next End Using End Sub ' StoreCache_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' If not yet cached, geocode specified address. Private Function GeoCode_(ByVal address As String) As Object() Dim result() As Object If Not m_cache.Contains(address) Then ' Address not cached yet. Load it. While result Is Nothing Dim ra() As String Dim response As WebResponse = HttpWebRequest.Create(String.Format( _ Query_2P, _ m_apiKey, _ address)).GetResponse() Using reader As StreamReader = New StreamReader(response.GetResponseStream()) ra = reader.ReadLine().Split(Chr(44)) End Using If ra(0) = "200" Then result = New Object() {CInt(ra(1)), CDbl(ra(2)), CDbl(ra(3))} ElseIf ra(0) = "620" Then ' Too many queries. Increase delay between calls. m_delay += 100 Me.ComponentMetaData.FireWarning( _ 0, _ "Geocode", _ "Geocode service has too many queries. Delay between calls is incremented.", _ String.Empty, _ 0) Else ' Failed to geocode. Throw New ApplicationException(String.Format( _ "Failed to geocode '{0}'. Received code: {1}", _ address, _ ra(0))) End If Call Thread.Sleep(m_delay) End While ' Address was successfully geocoded. Cache it. m_cacheModified = True m_cache.Add(address, result) End If GeoCode_ = CType(m_cache(address), Object()) End Function ' GeoCode_ #End Region ' Internals #Region "Attributes" Private m_addressColumn As String Private m_cacheFile As String Private m_apiKeyVariable As String Private m_addressIndex As Integer Private m_apiKey As String Private m_cache As Hashtable Private m_cacheModified As Boolean ' Milliseconds to wait between geocode calls. ' It will gradually increase if service responds too many queries (620). Private m_delay As Integer = 200 #End Region ' Attributes End Class ' ScriptMain