Correct IBM Data Provider Mapping Files for SSMS Import Wizard

My first attempt at using the Microsoft SSMS SQL Server Import and Export Wizard to import AS400 files onto the SQL Server went very poorly.  After much digging and searching, I was able to find the problems in the XML mapping files provided by IBM.

Before starting, make sure you have installed the IBM .Net providers when you installed Client Access or iSeries Access.  They will appear in the SQL MAPPINGFILE folder (see below), and you will see them listed as a Data Source in the SQL Server Import and Export Wizard.

Depending on your version of SQL Server Management Studio, the SSIS subfolder will have a different name. I am using SSMS V19, so my “MappingFiles” folder resides under \160 in this directory: C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\CommonExtensions\Microsoft\SSIS\160. When you open the “MappingFiles” folder, you should see the three IBM Providers. Before making changes, copy the three providers to a new folder. I used MappingFiles-OLD in the “160” parent folder. You cannot rename the files and keep them in the same parent folder since the wizard will search ALL mapping files within the “MappingFiles” folder.

A minimum of TWO CHANGES are need for the IBM XML files to work correctly. First, a change to the SOURCETYPE name is required. Second, you may encounter DATA TYPE conversion errors where a data type is not explicitly defined in the mapping file. I used NOTE++ to edit the XML files.

The SOURCETYPE is within the heading of each XML file. It presently reads IBMDADB2*. The “DB2” suffix will prevent the files from being recognized. Change the type to IBMDA*. This change will pick-up the IBMDA400, IBMDASQL, and IBMDARLA mapping file names.

DATATYPE Mapping Errors:

1. When you attempt to Import with the WIZARD, you will reach a screen where you can “EDIT MAPPINGS”.  Any datatype that cannot be mapped will show with Question Marks.  Click on the Field in Error to see the datatype that is missing from the XML mapping file.  Before I corrected the missing datatype for my character fields, the field’s TYPE was showing “????” or “Unknown”. When you click on the field, the “source column” information below will show the datatype that is missing. In my case, the mapping was looking for “CHARACTER”.

To correct the unknown datatype, I edited the mapping file and found an entry for “CHAR”. I copied the translation code and renamed it to “CHARACTER”. That resolved the mapping problem. Of all of the AS400 to SQL importing testing, this has been the only change that was needed. When you make a change, make sure you edit and change ALL THREE IBMDA files.