I admit it: I use SQL Server 2000 at work. It acts as my job scheduler, and it has handled my daily spatial and tabular data transformations for over three years. Most of the work is done by Data Transformation Services (DTS) packages and a vbs script that calls FME.

Those of you who have used Safe Software’s FME Workbench know that it is a great tool for performing spatial extract/transform/load (ETL) operations. It’s a unique way of setting up extremely powerful location data transformations without having to write a single line of code (but you can… in TCL and, more recently, Python). Workbench is an environment similar to DTS and ESRI’s Model Builder; FME’s implementation just happens to be more user-friendly and powerful:

Two recent developments in this field have made me into one extremely happy camper.

The first of these developments is the new version of DTS packaged with SQL Server 2005, which contains enough upgrades that it warrants a fancy new name: SQL Server Integration Services (SSIS). With this release, Microsoft has introduced a two-plane design paradigm, with the outer plane controlling process flow, and the inner plane controlling data flow. I can’t cover much here; suffice to say that it’s extremely powerful, easy to use, and that this feature alone makes the upgrade from 2000 worthwhile. The Microsoft SQL Server Developer Center is a good place to start learning more about SSIS if you’re interested.

The second of the recent developments ties into the first. Safe Software has done a soft launch on a beta FME SSIS plugin that encapsulates all of the cool Spatial ETL capabilities of FME inside SQL Server! Now I can set up a central SSIS workflow that manages all of my spatial transformations. I can also (and this is a big plus) make use of SQL Server’s native data transformation capabilities. This means that I can join disparate tabular data mid-transform to various spatial data sources before outputting a final product. I can also set up rollback routines without resorting to complicated scripting.

The Safe engineers have done a great job on this integration. Apart from some very FME-ish property and data source dialogs (which is a good thing™) it works seamlessly within the context of SSIS. I am happy to see that they managed to get the visualiser to work from within SSIS as well.

Here are some example shots from their web page:

Opening an ArcSDE data source

Setting the properties of a LengthCalculator transformer

Running the SSIS package (ohhh pretty colours… would like to see this in Workbench)

Viewing spatial results in FME Viewer

Viewing CSV results in text editor

Incidentally, this is not the only integration project that Safe has done. If you use Informatica they have also set up a beta program for FME integration with PowerCenter. If you’re an ArcGIS user, the Data Interoperability Extension gives you the same kind of integration with Model Builder (along with native data access to dozens of formats not supported directly by ESRI) and the 9.2 edition will come with a refresh to the FME 2006 GB code base.

I am amazed at Safe’s dedication to the leading edge. First there was Google Earth KML support, next came support for the SDF+ data format (an open format used for MapGuide Open Source and by Autodesk in its geospatial applications) and now this. What’s next? GeoRSS support?

-J

P.S. If you haven’t signed up for the First Annual FME User Conference, do it now. Vancouver is a great city, and I’m sure we’re going to have lots of fun learn lots of useful stuff there.

Related posts

• No related posts.