|
|
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.
|