The Import Export wizard uses a mapping file to compare and map the data types between the source and destination. This file is shared in the following location:
- For 64 bit platforms: C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\*.xml
- For 32 bit platforms: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\*.xml
When you run the wizard, it picks up a mapping file based on the names of the Source and Destination providers you selected, parses the SourceType and DestinationType tags from these XML files and populates the Type column in the
Column Mappings dialog box. When the mapping file is either not available or it does not contain the exact match for source-to-destination type mapping you can run into various issues mentioned in the Symptoms section.
You can go through the following scenarios for more information:
Scenario 1: The expected mapping file is not chosen by the wizard. The Mapping file may not work if the exact name for the Source Provider or Destination Provider is not listed in the corresponding XML tags and properties inside the mapping file.
For example if you are using the wizard to export data from IBMDB2 into SQL Server 2008, and the provider is an OLEDB provider for IBMDADB2, the two files IBMDB2ToSSIS10.XML IBMDB2ToSMSSQL10.XML should hopefully be parsed, however they may not be parsed if the provider names do not exactly match.
- IBMDB2ToSMSSQL10.XML would be useful for the wizard to understand the data types which do not require any conversions to reach an appropriate destination type in SQL Server 2008 via the Native Client 10.0 provider.
- IBMDB2ToSSIS10.XML would be useful for the wizard to understand the data types which do require a data type conversion to reach an appropriate destination type in SQL Server 2008. The IBM DB2 type has to be converted to SSIS in-memory DT_* types and a similar second mapping file (corresponding to the destination provider) will be used for conversion from DT_ * SSIS types back to the destination provider type.
In the scenario that the provider has a different name, such as an installation specific instance MyServerNameIBMDADB2, the provider name will not match with the default XML file tag SourceType="IBMDADB2".
To correct this OLE DB provider name mismatch, you can manually edit the .XML file and append the installation specific OLEDB provider name in a semicolon delimited list. For example you can edit the SourceType property in the IBMDB2ToMSSql10.XML as follows:
SourceType="IBMDADB2;MyServerNameIBMDADB2"
Note: You can note and confirm that the correct mapping file is being is used for transfer on the last dialog box of the wizard titled
Complete the Wizard.
The text will read
Provider mapping file: <path to the mapping file> or
Mapping file (to SSIS Type): <path to mapping file>.
When a mapping file that matches the source and destination providers selected for transfer is not found by the wizard, the last dialogue page will read "
Cannot locate the mapping file to map the provider types to SSIS types" and the Finish button will be disabled or grayed out and you cannot complete the wizard.
Scenario 2:
Even if the appropriate mapping file is found, there are times where the data type that needs to be mapped is not present in the mapping file, and the Import Export wizard is not able to resolve the missing type. This is the scenario where you would see a number for unresolved data type in the Type column of the wizard. In this scenario, you can manually add a new XML tag to the corresponding mapping file, to allow the wizard to automatically map the source enumerated type <numeric value> to a required destination type.
For example, if the Type is enumerated as 130 and the destination type needs to be mapped to ntext you can add the following tag in the IBMDB2ToSSIS10.XML
<!-- DBTypes for NChar, NVarChar, Text -->
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>130</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>ntext</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
Note: Before making any changes to the original mapping file you should always make a copy of the same as small mistakes in editing these files will make them unusable by SSIS.
Note: Editing the XML files can be a difficult and precision task. If there is a similar type in the same XML file, you may want to use that tag structure to help build the appropriate mapping tags for an unknown type. You must exit and restart the Import Export wizard for it to pick up any changes to the mapping files.
Scenario 3: In case no mapping file is available for the source or destination provider that is being used, you may have to manually create the mapping files if you need the wizard to automatically choose the data types for the problem columns. One file is used to match the provider data types to the SSIS data types, and a second file used to match the source provider data types to the destination provider types. Both files are required to use the wizard successfully with minimal intervention to correct Type mapping.