SQL Server Import/Export: cannot copy geo columns
It appears Microsoft forgot to include the geometry and geography datatypes in the SQL Server Import and Export Wizard.

There is an excellent explanation and fix here which I will summarize below:


Open the SQL mapping file. In my case I was doing am Export from SQL
Express 2014 to SQL Express 2008R2 using the SQL Client method. So
the file I had to edit is: C:\Program Files\Microsoft SQL
Server\120\DTS\MappingFiles\SqlClientToMSSql10.xml (In some cases
you might have to run Notepad first as administrator and then select
the file otherwise it doesn't allow you to save the changes)
Copy the following to the file, just before the line that reads &lt;/dtm:DatatypeMappings&gt;:

    &lt;!-- geography --&gt;
      &lt;dtm:DataTypeMapping &gt;
        &lt;dtm:SourceDataType&gt;
          &lt;dtm:DataTypeName&gt;geography&lt;/dtm:DataTypeName&gt;
         &lt;/dtm:SourceDataType&gt;
        &lt;dtm:DestinationDataType&gt;
          &lt;dtm:SimpleType&gt;
            &lt;dtm:DataTypeName&gt;DT_IMAGE&lt;/dtm:DataTypeName&gt;
          &lt;/dtm:SimpleType&gt;
        &lt;/dtm:DestinationDataType&gt;
      &lt;/dtm:DataTypeMapping&gt;

      &lt;!-- geometry --&gt;
      &lt;dtm:DataTypeMapping &gt;
        &lt;dtm:SourceDataType&gt;
          &lt;dtm:DataTypeName&gt;geometry&lt;/dtm:DataTypeName&gt;
        &lt;/dtm:SourceDataType&gt;
        &lt;dtm:DestinationDataType&gt;
          &lt;dtm:SimpleType&gt;
            &lt;dtm:DataTypeName&gt;DT_IMAGE&lt;/dtm:DataTypeName&gt;
          &lt;/dtm:SimpleType&gt;
        &lt;/dtm:DestinationDataType&gt;
      &lt;/dtm:DataTypeMapping&gt;