Recently, I encounter a issue when transfer a table with hierarchyid data type between two SQL Server 2008 R2 instances using SQL Server Import and Export Wizard. The error looks like this:
From the error message above, it was a SSIS Type unknown for hierarchyid data type in the SSIS mapping file C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML.
Then, I went to that directory and open MSSQLToSSIS10.XML mapping file, which is data type mappings between source data type and destination data type. However, I cannot find a data type mapping entry for hierarchyid data type, therefore I got above error because the SQL Server Import and Export Wizard does not know what data type to use for hierarchyid in the destination table.
I tried to manually create a mapping entry for hierarchyid data type using similar format as any other data types. To change this file we need to open it under an administration account security context, right-click the Notepad, run it as administrator, open MSSQLToSSIS10.XML file; and then add the following XML entry in the mapping file and save the mapping file:
<!-- hierarchyid --> <dtm:DataTypeMapping > <dtm:SourceDataType> <dtm:DataTypeName>hierarchyid</dtm:DataTypeName> </dtm:SourceDataType> <dtm:DestinationDataType> <dtm:SimpleType> <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName> </dtm:SimpleType> </dtm:DestinationDataType> </dtm:DataTypeMapping>
After that, when I tried to run SQL Server Import and Export to transfer that table again, I did not get the above error and table transferred successfully. Problem resolved
One more thing is that if we are running 64-bit OS, we may get both 32-bit and 64-bit SQL Server Import and Export Wizards. For 32-bit SQL Server Import and Export Wizard, update the related mapping file under C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\ folder; and for 64-bit or 32-bit OS, update it under C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\ folder.
Hope this helps.