Apr
7
2010

SSIS and spatial: A new article and some existing custom components

New 3-part walkthrough

Today (April 7, 2010), sql-server-performance.com published the 1st of a 3-part series of articles by BI blogger Siddarth Mehta, titled "Geocoding Text-Based Spatial Data for Use In SSIS Packages." The sample project combines the use of SSIS with the Bing Maps API and Web service.

Anything else out there?

It's not easy to find more information about using SQL Server Integration Services (SSIS) with spatial data!

  • If you search for "SSIS + mapping," of course you find dozens of posts about mapping data types.
  • If you search for "SSIS + spatial," you find the School of Surveying and Spatial Information Systems at the University of New South Wales.
  • If you search for "SSIS + geo," you find the "Soils Survey Information System." And so on.

[Off topic: For more amusement, glance through the pages and groups on Facebook for "SSIS." They include not only the Superintendent of Sanitary Services for Chile and the "Eminem Club for SSIS People," but the Student Sexuality Information Service at Brandeis University.]

There seem to be more custom components than there are walkthroughs and examples

There are the commercial components from Safe Software, "The Spatial ETL Experts." The site has some screen shots and demos. (The cost is not listed, but when I have to fill out a form for a salesperson to contact me, I know that it's more than I can afford as an individual developer!)

There are several custom components on Codeplex:

  • First and foremost, the Spatial Data Flow Components written by SSIS product team member and developer Bob Bojanic.
  • The MapPoint Batch Geocoder, "an open source transformation component for SQL Server Integration Services that performs batch geocoding of address information directly within the SSIS pipeline using the geocoding capabilities of MapPoint's Customer Data Services. The transformation outputs both the original input data and the geodetic spatial data using the new Spatial Data Types of SQL Server 2008."

If you read German, German SSIS blogger Tillman Eitelberg has 2 blog posts from 2009 about spatial data with SSIS:

It's getting easier to visualize spatial data in your applications

Bob Beauchemin talks about some of the new visualization features, especially in Reporting Services in SQL Server 2008 R2, in his MSDN Magazine article, Visualizing Spatial Data.

Let's hope (and vote?) for more support for spatial data in SQL Server "Denali"

Bob concludes his article with the wish: "Perhaps the next release of SQL Server will see more integration with SQL Server Analysis Services and even data mining features."

Naturally we hope to see more support in SSIS as well! By searching on "spatial," I found this related Connect item that you may want to consider voting on: Improve support for Spatial Data Sources.

-Doug