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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
1
Sign in
to vote
ID 525477 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/18/2010 5:27:42 AM
Access Restriction Public

Description

Hi there,
we have developed a OLEDB Provider to connect to SAP data.
The mapping of our types worked fine with Import/Export wizard in SQL Server 2005.

Since SQL Server 2008 the types are NOT mapped correct anymore.
The OLDB type ids are shown in the mapping dialog e.g. 130 for DBTYPE_WSTR.

So - SQL Server 2008 behavior has changed. The OLEDB types are no more mapped excactly.

Sign in to post a comment.
Posted by Microsoft on 6/22/2011 at 6:20 PM
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

Posted by Microsoft on 6/3/2011 at 3:40 PM
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!
Posted by Microsoft on 6/2/2011 at 5:08 PM
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.
Posted by Bob Boffin on 7/15/2010 at 2:18 AM
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))
Posted by ndee42 on 6/9/2010 at 5:01 AM
Any new results in this case?
Posted by ndee42 on 2/1/2010 at 5:55 AM
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.
Posted by Microsoft on 1/27/2010 at 10:45 AM
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.