Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Call NEWID Function in a User-Defined Function
SQL Server 2008

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

Importing or exporting a table containing a geography or geometry column should be straight forward using the Import and Export Wizard of SQL Server 2008 Management Studio.  Just like importing or exporting any other kind of table, you simply identify the server name and database of the data source, the server name and database of the destination, specify whether to copy one or more tables and views or to copy the results of a query from the data source, and finally select the tables and/or views to copy or specify the query to run.

Unfortunately, you might encounter an error, just like the following error, when your table contains a geography or geometry column:

At the bottom of the screen you’ll see a message regarding an error with the geography column type conversion.

Found 1 unknown column type conversion(s).
You are only allowed to save the package.

If your table has a geometry column type, you will also get the same error as this one.  Double-clicking on the source column that had the error, you will get the following message:

[Source Information]
Source Location : (local)
Source Provider : SQLNCLI10
Table: [dbo].[Country]
Column: CountryGeography
Column Type: geography
SSIS Type: (Type unknown ...)
Mapping file (to SSIS type): C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

[Destination Information]
Destination Location : (local)
Destination Provider : SQLNCLI10
Table: [dbo].[Country]
Column: CountryGeography
Column Type: geography
SSIS Type: (Type unknown ...)
Mapping file (to SSIS type): C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

[Conversion Steps]
Conversion unknown ...
SSIS conversion file: C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

From this error messages, you can see that it doesn’t recognize the geography column type.  If your table has a geometry column type, you will also get the same error.

Solution:

Looking at the Column Conversion Details window, it specifies a mapping file that it uses to convert one data type to another based on the provider of the source and destination.  In this particular case, it uses the file MSSQLToSSIS10.XML file under the C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles folder.

Opening this file, you will see that there's no data type mapping for both the new geography and geometry data types.  To solve the error with the importing or exporting of these data types, we have to manually define the mapping for both the geography and geometry data types.  We will use the definition for the varbinary(max) data type for the mapping of the geography and geometry data types.

The data type mapping definition for varbinary(max) is as follows:

  <!-- varbinary (max) -->
  <dtm:DataTypeMapping >
    <dtm:SourceDataType>
      <dtm:DataTypeName>varbinarymax</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
      <dtm:SimpleType>
        <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
      </dtm:SimpleType>
    </dtm:DestinationDataType>
  </dtm:DataTypeMapping>

Simply copy this data type mapping and paste it on the same file and simply change the varbinary(max) in the comment to geometry.  Aside from this, change the varbinarymax value in the dtm:DataTypeName for the dtm:SourceDataType to geometry.  The new data type mapping for the geometry data type will look as follows:

  <!-- geometry -->
  <dtm:DataTypeMapping >
    <dtm:SourceDataType>
      <dtm:DataTypeName>geometry</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
      <dtm:SimpleType>
        <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
      </dtm:SimpleType>
    </dtm:DestinationDataType>
  </dtm:DataTypeMapping>

Doing the same thing for the geography data type, the data type mapping for the geography data type will look like the following:

  <!-- geography -->
  <dtm:DataTypeMapping >
    <dtm:SourceDataType>
      <dtm:DataTypeName>geography</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
      <dtm:SimpleType>
        <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
      </dtm:SimpleType>
    </dtm:DestinationDataType>
  </dtm:DataTypeMapping>

Save the MSSQLToSSIS10.XML file and you should now be able to import or export a table that contains a geography and/or a geometry data type.  If it still doesn't work, try restarting the SQL Server service and the issue should go away.

Related Articles :