Today I had to make some work live that involved copying a database containing UK Postcodes and their related geo-location data to another SQL 2008 server.
The table contained a list of all UK Postcodes as well as their longitude and latitude and a GeoLocation column that was based on the new SQL 2008 DataType Geography.
However when I tried to use the Import/Export wizard to copy the data I got to the Review Data Type Mapping page and was met with the following error message.
Found 1 unknown column type conversion(s) You are only allowed to save the package.
Viewing the full error details revealed that the problem was down to SQL not understanding the Geography data type in the table I wanted to export.
The full error message details are below:
First of all I checked that the database compatibility mode was set to 100 (SQL 2008) and not 90 (SQL 2005) and once I had confirmed both databases were the correct format I checked the mapping conversion XML file on the server I was doing the import from.
This XML mapping file is located at the following path C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML.
On opening the file I could see that there was no mention of the geography OR geometry data types which explained why the DTS package wizard could not carry out the operation.
To fix this I copied one of the other similar data types (varbinary) and re-inserted it into the file twice before changing the names to Geography and Geometry.
You can just copy and paste the following XML into the file.
Save the MSSQLToSSIS10.XML file and you should now be able to import or export a table that contains the datatypes geography and geometry.
If for some reason after editing the file it still doesn't work, try the following:
- restarting the SQL Server service on the server you are running the export/import wizard from.
- restarting your own SQL Server Client Tools.
- If you are running a 64bit version of Windows 7 like I am then you might need to also edit the same file in the 32 bit Program folder e.g C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML.