locked
import export RRS feed

  • Question

  • Does DTS Wizard (import and export Data (32-bit)) work with spatial data?

    I am using sqlexpress 2008 and have looked into the import function. In the list I see an ESRI geodatabase OLE DB Provider. If I select properties and fill in the location and datasource for either and mdb geodb or a file geodb. But as soon as I hit test connection I send microsoft an error report and import export goes away. At this point I am thinking the ole db is for flat data and not geo data. Is it possible to use this tool for spatial data?

    thanks

     

    Tuesday, May 4, 2010 8:28 PM

Answers

  • Last I knew, the Import/Export wizard had some issues importing spatial datatypes - it's not specifically related to the OLE DB provider (you couldn't even directly copy a DB containing spatial datatypes from one SQL Server instance to another), but rather because the geography and geometry datatypes are implemented as CLR UDTs. There are various workarounds available but these generally involve manually editing the SSIS data type mapping or casting geography or geometry data as varbinary(max) and importing them as raw binary. I suspect you won't be able to do this using SQL Express, however, since you'd need the full SSIS editor.

    However, this problem normally results only in a warning message ("unknown column type conversion", or something like that) - not a full-blown crash out as it seems you're describing. That sounds more like there's a problem with your driver. Are you using the ESRI OLEDB driver that came with ArcGIS? What version?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BradGISOkla Wednesday, May 5, 2010 1:34 PM
    Wednesday, May 5, 2010 7:11 AM
    Answerer

All replies

  • Last I knew, the Import/Export wizard had some issues importing spatial datatypes - it's not specifically related to the OLE DB provider (you couldn't even directly copy a DB containing spatial datatypes from one SQL Server instance to another), but rather because the geography and geometry datatypes are implemented as CLR UDTs. There are various workarounds available but these generally involve manually editing the SSIS data type mapping or casting geography or geometry data as varbinary(max) and importing them as raw binary. I suspect you won't be able to do this using SQL Express, however, since you'd need the full SSIS editor.

    However, this problem normally results only in a warning message ("unknown column type conversion", or something like that) - not a full-blown crash out as it seems you're describing. That sounds more like there's a problem with your driver. Are you using the ESRI OLEDB driver that came with ArcGIS? What version?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BradGISOkla Wednesday, May 5, 2010 1:34 PM
    Wednesday, May 5, 2010 7:11 AM
    Answerer
  • I assume it is the driver provided, however I do have FME (esri version) which could possibly have something in it. Yes it is OLE_DB. The versions are 9.3.1 and fme desktop 2010. I suspected that the import export tool might not work on spatial objects  especially from outside dbs. I have several tools to accomplish this. I am exploring what I like the best. In oracle I used raptor quite a bit to explore the spatial metadata and indexes and liked it. My sop may end up being autodesk map fdo2fdo bulk copy. 

    Thanks

     

    Wednesday, May 5, 2010 1:34 PM
  • Try the workaround in the link for the Import/Export wizard errors. 

     Error in Importing/Exporting Geography/Geometry Columns in SQL Server 2008 Management Studio

    I just tried it yesterday and it worked for moving spatial tables between dbs. 

    It seems you are trying to do something different. You want to convert spatial data in ESRI format from an ESRI PGDB(mdb) or a FGDB to SQL Spatial, which SQL Import/Export Wizard will not do. There are tools to do that like FME and Shape2SQL.

     

     

    Wednesday, May 5, 2010 3:28 PM
  • If you have FME, I'd run it from within FME workbench, but it's good to explore other possibilities as you say. Please let us know if/when/how you get it working!

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, May 5, 2010 5:33 PM
    Answerer