SQL Server 2008 Import/Export Wizard type mapping fails by ndee42

Please consult the KB article, http://support.microsoft.com/kb/2152728/en-US, which instructs you on creating mapping files for customized providers. You may also manually choose the data types in the mapping editor UI. If you are still having issues, please let us know.

-- The SSIS Team

To better troubleshoot this issue and provide better experience in the future release, please provide the following information.

1. In your case, the SAP data is the source or the destination in the Import & Export wizard? If SAP data is the source, what provider is the destination? If SAP data is the destination, what provider is the source?

2. Is DBTYPE_WSTR the only type that has problems?

Thanks!

This is a known issue with detail in the KB article http://support.microsoft.com/kb/2152728/en-US.

For the issue in the repro section, the workaround is as described in 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.

I have found a similar problem when trying to transfer data between two SQL Server 2008 databases. The wizard does not recognise the Time datatype (actually Time(7))

Any new results in this case?

None of the types are mapped correct. The import wizard in vc2008 shows "130" for exmple as type for a DBTYPE_WSTR.

I have attached a PDF with screenshots.

Can you provide information on the types that are not being mapped correctly. Please provide the source type, OLEDB id and any other information you have to make it easier to troubleshoot this issue. In addition, what errors did you encounter when you tried the mapping file.