SSIS 2008 Import Export Wizard can show numbers instead of data type

Symptoms

When using the SQL Server 2008 Import Export wizard to copy data from a third party OLEDB provider into a destination such as SQL Server, you may note that some of the columns do not get mapped with the proper data types. Instead, a numerical integer value may appear in the Type column in the   Column Mappings window, such as the number 131 for the source's numeric column, or 135 for a source column whose data type is timestamp.

Sometimes the value -1 appears in the data type column.

Cause

This is a known problem with the SQL Server 2008 Import Export wizard. The wizard is not aware of all possible types in all providers, so it may present unexpected numerical values when non-matching types are encountered..

Workaround

Currently there is no hotfix available for this problem.
You can workaround the problem by using one of the following methods:
  • Method 1: For each of the columns whose Type is incorrectly shown in the Column Mappings dialog box, manually edit the Type drop down box to the correct type.
  • Method 2: Edit the mapping files to better suit the providers and data types being used, so that the mapping files will automatically map the problem data types for a future execution of the wizard.

More Information

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.
Propiedades

Id. de artículo: 2152728 - Última revisión: 15 sept. 2010 - Revisión: 1

Comentarios