If you are working with spatial data types, then it won’t be long before you learn that SSIS does not directly support geography or geometry SQL Server data types. However, LeapFrogBI does have the tools needed to make integrating spatial data seamless.
Here’s the step by step processes for collecting spatial data from a SQL Server source and integrating it with the rest of your data model.
1. In your source profile cast the spatial data to either binary or text data. This enables SSIS to consume the data without mapping it to an image data type. While you could let SSIS consume the data as an image, the data will need to be cast to binary or text prior to converting it back to a spatial data type.
Geometry or Geography:
– cast(FieldName.STAsBinary() as varbinary(8000))
– cast(FieldName.STAsText() as nvarchar(4000))
2. In any component downstream of your stage component create an expression that cast the binary or text field back to a spatial data type using the appropriate conversion function.
That’s all there is to it! Your spatial data types are now handled just like any other data type in LeapFrogBI. Keep in mind that spatial data types are not comparable. If we had been able to stage data as geography or geometry, we would still need to cast them out to a comparable data type to include them in the tracked changes fields in PSA. So, it is not such a bad thing that SSIS doesn’t support spatial data types for this reason. Now, if SQL Server could perform comparison on spatial data types, that would be even better; maybe in a future release…
