Spatial data has definitely got some level of support in SSMS, but that is more limited in terms of visualization of data in the results tab. When I consider to move the same data out of SQL Server i.e when I want to import and/or export this data using the Import Export Wizard, it breaks up as it is not upgraded with a corresponding data type in SSIS.
Even if you modify the config file that Import Export Wizard uses to map it to any data-type and facilitate the data movement, it does not help. Using varchar(max) or varbinary(max) also helps for some destination and breaks for others. To the best of my knowledge, I am not able to figure out a generic datatype that can take the helm of pulling or pushing any spatial datatype on it's back for a smooth import - export.
The same issue is faced in SSIS. As of date, there is no straight mapping for spatial data types. Though spatial datatypes got introduced in SQL Server 2008, SSIS has not been upgraded even in the R2 version to accommodate smooth movement of spatial data. DT_IMAGE is the best option as of now to move the same in SSIS, or the best approach is to convert spatial data in WKT format before it leaves SQL Server and read the same in SSIS. Converting in WKT makes it easier to analyze and manipulate this data in SSIS and then use a Script task or the same WKT to push data back into the spatial columns.
Acceptance of spatial datatypes and it's use would grow gradually, but R2 would have been good release to incorporate the support for spatial data movement in and out of SQL Server as well as in SSIS too.
Article 
2 comments:
We were able to import/export spatial data types after applying changes from this site: http://www.sql-server-helper.com/sql-server-2008/import-export-unknown-column-type-geography-geometry.aspx
I agree that SSIS is behind the curve today for spatial data types. We have been evaluating the FME tool from Safe Software which integrates into SSIS and also trolling CodePlex for ideas.
I am aware of this link, but that would work for some destinations, not for all. It would work with SQL Server as destination/source for import/export but it would break either in Flat file / Excel or other source/destination. Using DT_IMAGE is not a generic solution for all source/destination.
Post a Comment