Geocode Locations Using Google Maps v3 API and SSIS

I wanted to do a cool experiment on SSIS that I haven’t tried before. I am really interested in this whole mapping component on SSRS, and I know that most likely the biggest challenge of companies is how to geocode addresses that are already in their databases. So what I wanted to do was to try and retrieve lat/long information from public geocoding web services within SSIS.

Please note that the exercise below is purely for experimentation. Please read the restrictions of each of the geocoding services, and go with whichever is appropriate for your purpose.

Also this is a simplistic code to show you how to get the latitude/longitude information. On your own versions, test thoroughly and add the appropriate exception handlers.

Ok, let’s start.

Step 1 – Look for the appropriate geocoding service.

There are few publicly available ones. Some are limited in content, some are very restricted. Check out the summary here – http://en.wikipedia.org/wiki/Geocoding

For this experiment I am going to use Google Maps v3 API since it’s publicly available, and V3 doesn’t need the Maps API key anymore! Please make sure you read the restrictions of using the Google Maps v3 API before deciding to use it in your applications/systems.

Note that for v3, there are only 2 available output formats: json and xml. Previous version of the API supported other output formats like kml and csv. I am going to stick with XML. (I’ll leave JSON for the next experiment).

Here is an example URL and result: http://maps.googleapis.com/maps/api/geocode/xml?address=Pacific+West+Outlet,Gilroy,California,US&sensor=false

Try to study the structure, it will help later on with the parsing for latitude and longitude.

Step 2 – Prep your package

Create your SSIS package. Drop a data flow task, and add your source. In my case my source is an OLE DB source pointing to my SQL Server table that has address fields already.


Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (15 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 4 votes)

Generating Random Data for SQL Server and MySQL

SQL Server – uses NEWID() in ORDER BY

   1: -- using the AdventureWorks Database
   2: SELECT
   3:     TOP 10
   4:     DepartmentID,
   5:     [Name]
   6: FROM
   7:     HumanResources.Department
   8: ORDER BY
   9:     NEWID()
  10:

MySQL – uses RAND() in ORDER BY

   1: SELECT
   2:   `Code`,
   3:   `Name`
   4: FROM
   5:   `Country`
   6: ORDER BY
   7:   RAND()
   8: LIMIT 10;
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Using DDL Triggers

Sample script below:

   1: -- ==========================================================================
   2: -- Object       : DDLTrigger.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Notes        : 
   6: -- ==========================================================================
   7:
   8: -- DDL Triggers
   9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
  10:
  11: -- DDL Trigger Events
  12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
  13:
  14: -- EVENTDATA
  15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
  16: -- EVENTDATA function returns XML data which contains the information about server 
  17: -- or database events. 
  18:
  19: -- sample EVENTDATA()
  20: /*
  21: <EVENT_INSTANCE>
  22:   <EventType>CREATE_TABLE</EventType>
  23:   <PostTime>2008-10-05T01:23:12.030</PostTime>
  24:   <SPID>55</SPID>
  25:   <ServerName>JUBILEESQL01</ServerName>
  26:   <LoginName>JUBILEEAdministrator</LoginName>
  27:   <UserName>dbo</UserName>
  28:   <DatabaseName>AdventureWorks</DatabaseName>
  29:   <SchemaName>dbo</SchemaName>
  30:   <ObjectName>t</ObjectName>
  31:   <ObjectType>TABLE</ObjectType>
  32:   <TSQLCommand>
  33:     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
  34:                 QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
  35:     <CommandText>CREATE TABLE t
  36:         (
  37:             id int
  38:         )
  39: </CommandText>
  40:   </TSQLCommand>
  41: </EVENT_INSTANCE>
  42:
  43:  */
   44:
  45: -- ---------------------------------------------------------------------
  46: -- database scope
  47: -- SSMS: Database > Programmability > Database Triggers
  48: -- ---------------------------------------------------------------------
  49: USE AdventureWorks
  50: GO
  51:
  52: IF EXISTS(
  53:   SELECT *
  54:     FROM sys.triggers
  55:    WHERE name = N'tr_db_ddl_NoCreateTable'
  56:      AND parent_class_desc = N'DATABASE'
  57: )
  58: BEGIN
  59:     DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
  60: END
  61: GO
  62:
  63: CREATE TRIGGER tr_db_ddl_NoCreateTable
  64: ON DATABASE -- current database
  65: FOR CREATE_TABLE, DROP_TABLE
  66: AS
  67: BEGIN
  68:     SELECT
  69:         'tr_db_ddl_NoCreateTable' 'Trigger',
  70:         'You are not permitted to perform selected action' 'Msg2',
  71:         EVENTDATA() 'EVENTDATA()'
  72:     ROLLBACK
  73: END
  74: GO
  75:
  76: -- test
  77: CREATE TABLE t
  78: (
  79:     id int
  80: )
  81: GO
  82:
  83:
  84: -- ---------------------------------------------------------------------
  85: -- server scope
  86: -- SSMS: Server Objects > Database Triggers
  87: -- ---------------------------------------------------------------------
  88: USE AdventureWorks
  89: GO
  90:
  91: IF EXISTS(
  92:    SELECT *
  93:    FROM sys.server_triggers
  94:    WHERE name = N'tr_srv_ddl_DisplayServerEvents'
  95: )
  96: BEGIN
  97:     DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
  98: END
  99: GO
 100:
 101:
 102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
 103: ON ALL SERVER
 104: FOR CREATE_DATABASE, CREATE_LOGIN
 105: AS
 106: BEGIN
 107:     SELECT
 108:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
 109:         'You are not permitted to perform selected action' 'Msg',
 110:         EVENTDATA() 'EVENTDATA()'
 111:     ROLLBACK
 112: END
 113: GO
 114:
 115: -- test
 116: CREATE DATABASE db
 117: GO

It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS

Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.

The trigger that fails follows:

   1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
   2: ON ALL SERVER
   3: FOR DDL_SERVER_SECURITY_EVENTS
   4: AS
   5: BEGIN
   6:     SELECT
   7:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
   8:         'You are not permitted to perform selected action' 'Msg',
   9:         EVENTDATA() 'EVENTDATA()'
  10:     ROLLBACK
  11: END
  12: GO
  13:

Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.

DDL Trigger Events

Will continue to dig in; when I find additional details I will post a follow up entry to this one.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Using the OUTPUT clause in SQL Server 2005

   1: -- ==========================================================================
   2: -- Object       : Output.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Origin       : 2008/06/17
   6: -- Last Modified: 2008/10/04
   7: -- Notes        : 
   8: -- ==========================================================================
   9:
  10: -- The OUTPUT clause allows you to display or manipulate rows affected by 
  11: -- INSERT/UPDATE/DELETE
  12: -- Similar to the inserted and deleted tables in a trigger
  13:
  14:
  15: USE AdventureWorks
  16: GO
  17:
  18: IF OBJECT_ID('dbo.DepartmentCopy') IS NOT NULL
  19:   DROP TABLE dbo.DepartmentCopy
  20: GO
  21:
  22: SELECT
  23:     *
  24:     INTO dbo.DepartmentCopy
  25: FROM
  26:     HumanResources.Department
  27:
  28:
  29: -- ---------------------------------------------------------------------
  30: -- display deleted values
  31: -- ---------------------------------------------------------------------
  32: DELETE dbo.DepartmentCopy
  33:     ---------------------------------------------
  34:     OUTPUT 
  35:         deleted.DepartmentID        AS 'Deleted ID',
  36:         deleted.[Name]              AS 'Deleted Name'
  37:     ---------------------------------------------
  38: WHERE 
  39:     DepartmentID > 10
  40:
  41:
  42:
  43: -- ---------------------------------------------------------------------
  44: -- display inserted values PLUS capture inserted values 
  45: -- ---------------------------------------------------------------------
  46: DECLARE @InsertedDepartment TABLE
  47: (
  48:    DepartmentID    smallint,
  49:     [Name]         varchar(50)
  50: )
  51:
  52: INSERT INTO dbo.DepartmentCopy([Name], GroupName, ModifiedDate)
  53:     ---------------------------------------------
  54:     OUTPUT
  55:         inserted.DepartmentID        AS 'Inserted ID',
  56:         inserted.[Name]              AS 'Inserted Name'
  57:     INTO @InsertedDepartment (DepartmentID, [Name])
  58:     ---------------------------------------------
  59: SELECT
  60:     [Name],
  61:     GroupName,
  62:     GETDATE()
  63: FROM
  64:     HumanResources.Department
  65: WHERE
  66:     DepartmentID > 10
  67:
  68: -- display what is captured
  69: SELECT
  70:     *
  71: FROM
  72:     @InsertedDepartment
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Script to Drop/Recreate CHECK Constraints

   1: -- ===========================================================
   2: -- Check Constraints
   3: -- How to script out Check Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- Drop Check Constraints
   8: SELECT
   9:     'ALTER TABLE  ' +
  10:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(CONSTRAINT_NAME)
  14: FROM
  15:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  16:     INNER JOIN sys.sysobjects so
  17:     ON cc.CONSTRAINT_NAME = so.[name]
  18:
  19: -- Recreate Check Constraints
  20: SELECT
  21:     'ALTER TABLE  ' +
  22:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  23:     CHAR(10) +
  24:     ' ADD CONSTRAINT ' +
  25:     QuoteName(CONSTRAINT_NAME) +
  26:     ' CHECK ' +
  27:     CHECK_CLAUSE
  28: FROM
  29:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  30:     INNER JOIN sys.sysobjects so
  31:     ON cc.CONSTRAINT_NAME = so.[name]

Here is a sample result:

ALTER TABLE  [ProductReview]
 DROP CONSTRAINT [CK_ProductReview_Rating]
ALTER TABLE  [ProductReview]
 ADD CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLCMD Examples

Here are some samples on how to use SQLCMD in SSMS.

   1: -- ==========================================================================
   2: -- Object       : SQLCMD-SSMS.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/01
   7: -- Last Modified: 2008/08/05
   8: -- Notes        : 
   9: --               This needs to be run in SSMS in SQLCMD mode
  10: -- ==========================================================================
  11:
  12: -- SHORTCUTS
  13: -- F1 Help
  14: -- Ctrl+ Shift+M replace template parameters
  15:
  16: -- --------------------------------------------------------------------------
  17: -- How to use an output file to store query output
  18: -- --------------------------------------------------------------------------
  19: :out C:Tempsampleoutput.txt
  20:
  21: SET NOCOUNT ON
  22:
  23: SELECT
  24:     @@VERSION AS 'Server Version',
  25:     GETDATE() AS 'Current Date'
  26:
  27: SET NOCOUNT OFF
  28:
  29:
  30: -- --------------------------------------------------------------------------
  31: -- How to set a variable
  32: -- Note variables must be run in the same batch
  33: -- --------------------------------------------------------------------------
  34:
  35: -- sample 1
  36: :setvar dbname AdventureWorks
  37: :setvar col1 DepartmentID
  38: :setvar col2 Name
  39: :setvar tablename HumanResources.Department
  40:
  41: SELECT '$(dbname)' AS dbname
  42:
  43: -- use the database
  44: USE $(dbname)
  45: SELECT
  46:     DB_NAME() AS 'DB_NAME()',
  47:     DB_ID()   AS 'DB_ID()'
  48:
  49: -- select from the table
  50: SELECT
  51:     $(col1),
  52:     $(col2)
  53: FROM
  54:    $(tablename)
  55:
  56:
  57: -- sample 2
  58: -- here we are setting several environment variables
  59: :setvar workpath C:Temp
  60: :setvar outfile out.txt
  61: :setvar errfile err.txt
  62: :out      $(workpath)$(outfile)
  63: :Error    $(workpath)$(errfile)
  64:
  65: -- output of this will go to file specified in :out
  66: SELECT @@VERSIONS
  67:
  68:
  69: -- --------------------------------------------------------------------------
  70: -- How to execute a script file
  71: -- --------------------------------------------------------------------------
  72:
  73: -- sample 1
  74: -- connect to a different database
  75: -- make sure you are in SQLCMD mode
  76: USE test
  77: :r Y:DropTable.sql
  78: GO
  79: :r Y:CreateTable.sql
  80: GO
  81:
  82: SELECT
  83:     DB_NAME()    AS 'Database',
  84:     [name]        AS 'Table',
  85:     create_date    AS 'Created',
  86:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'
  87: FROM
  88:     sys.objects
  89: WHERE
  90:     type = 'U'
  91: ORDER BY
  92:     create_date DESC
  93:
  94: -- sample2
  95: :setvar dbname Test
  96: :setvar workpath Y:
  97: :setvar file1 DropTable.sql
  98: :setvar file2 CreateTable.sql
  99: :setvar errfile err.txt
 100:
 101: USE $(dbname)
 102: :r $(workpath)$(file1)
 103: :r $(workpath)$(file2)
 104:
 105: SELECT
 106:     DB_NAME()    AS 'Database',
 107:     [name]       AS 'Table',
 108:     create_date   AS 'Created',
 109:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'
 110: FROM
 111:     sys.objects
 112: WHERE
 113:     type = 'U'
 114: ORDER BY
 115:     create_date DESC
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`