{"id":470,"date":"2023-08-10T18:10:17","date_gmt":"2023-08-11T01:10:17","guid":{"rendered":"https:\/\/tmainc.net\/?p=470"},"modified":"2023-08-11T09:24:09","modified_gmt":"2023-08-11T16:24:09","slug":"correct-ibm-data-provider-mapping-files-for-ssms-import-wizard","status":"publish","type":"post","link":"https:\/\/tmainc.net\/?p=470","title":{"rendered":"Correct IBM Data Provider Mapping Files for SSMS Import Wizard"},"content":{"rendered":"\n<p>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.&nbsp; After much digging and searching, I was able to find the problems in the XML mapping files provided by IBM.<\/p>\n\n\n\n<p>Before starting, make sure you have installed the IBM .Net providers when you installed Client Access or iSeries Access.&nbsp; 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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"270\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\" alt=\"\" class=\"wp-image-471\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png 624w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-300x130.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-100x43.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-150x65.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-200x87.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-450x195.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26-600x260.png 600w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<p>Depending on your version of SQL Server Management Studio, the SSIS subfolder will have a different name. I am using SSMS V19, so my \u201cMappingFiles\u201d 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 \u201cMappingFiles\u201d 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 \u201c160\u201d 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 \u201cMappingFiles\u201d folder.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"268\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27.png\" alt=\"\" class=\"wp-image-472\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27.png 624w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-300x129.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-100x43.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-150x64.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-200x86.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-450x193.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-27-600x258.png 600w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"218\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28.png\" alt=\"\" class=\"wp-image-473\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28.png 624w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-300x105.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-100x35.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-150x52.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-200x70.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-450x157.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-28-600x210.png 600w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"178\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29.png\" alt=\"\" class=\"wp-image-474\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29.png 624w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-300x86.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-100x29.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-150x43.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-200x57.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-450x128.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-29-600x171.png 600w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<p>A minimum of TWO CHANGES are need for the IBM XML files to work correctly.  First, a change to the <strong>SOURCETYPE<\/strong> name is required. Second, you may encounter <strong>DATA TYPE<\/strong> conversion errors where a data type is not explicitly defined in the mapping file.   I used NOTE++ to edit the XML files.<\/p>\n\n\n\n<p>The&nbsp;<strong>SOURCETYPE&nbsp;<\/strong>is within the heading of each XML file. It presently reads IBMDADB2*. The &#8220;DB2&#8221; 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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30.png\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"214\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30.png\" alt=\"\" class=\"wp-image-475\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30.png 624w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-300x103.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-100x34.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-150x51.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-200x69.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-450x154.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-30-600x206.png 600w\" sizes=\"(max-width: 624px) 100vw, 624px\" \/><\/a><\/figure>\n\n\n\n<p><strong>DATATYPE Mapping Errors<\/strong>:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>When you attempt to Import with the WIZARD, you will reach a screen where you can \u201cEDIT MAPPINGS\u201d.\u00a0 Any datatype that cannot be mapped will show with Question Marks.\u00a0 Click on the Field in Error to see the datatype that is missing from the XML mapping file.\u00a0 Before I corrected the missing datatype for my character fields, the field&#8217;s TYPE was showing \u201c????\u201d or \u201cUnknown\u201d. When you click on the field, the \u201csource column\u201d information below will show the datatype that is missing. In my case, the mapping was looking for \u201cCHARACTER\u201d.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31.png\"><img loading=\"lazy\" decoding=\"async\" width=\"599\" height=\"504\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31.png\" alt=\"\" class=\"wp-image-476\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31.png 599w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31-300x252.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31-100x84.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31-150x126.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31-200x168.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-31-450x379.png 450w\" sizes=\"(max-width: 599px) 100vw, 599px\" \/><\/a><\/figure>\n\n\n\n<p>To correct the unknown datatype, I edited the mapping file and found an entry for \u201cCHAR\u201d. I copied the translation code and renamed it to \u201cCHARACTER\u201d. 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\u00a0<strong>change ALL THREE IBMDA files<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32.png\"><img loading=\"lazy\" decoding=\"async\" width=\"762\" height=\"428\" src=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32.png\" alt=\"\" class=\"wp-image-491\" srcset=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32.png 762w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-300x169.png 300w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-100x56.png 100w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-150x84.png 150w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-200x112.png 200w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-450x253.png 450w, https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-32-600x337.png 600w\" sizes=\"(max-width: 762px) 100vw, 762px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>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.&nbsp; After much digging and searching, I was able to find the problems in the XML &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/tmainc.net\/?p=470\"> <span class=\"screen-reader-text\">Correct IBM Data Provider Mapping Files for SSMS Import Wizard<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[3,5,8,4,6,7],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-as400","tag-as400","tag-ibm-data-provider","tag-import","tag-iseries","tag-sql","tag-ssms"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/tmainc.net\/?p=470\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.\" \/>\n<meta property=\"og:description\" content=\"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.&nbsp; After much digging and searching, I was able to find the problems in the XML &hellip; Correct IBM Data Provider Mapping Files for SSMS Import Wizard Read More &raquo;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/tmainc.net\/?p=470\" \/>\n<meta property=\"og:site_name\" content=\"TMA Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2023-08-11T01:10:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-08-11T16:24:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\" \/>\n<meta name=\"author\" content=\"tommanntma\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@tommanntma\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"tommanntma\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/tmainc.net\/?p=470\",\"url\":\"https:\/\/tmainc.net\/?p=470\",\"name\":\"Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.\",\"isPartOf\":{\"@id\":\"https:\/\/tmainc.net\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/tmainc.net\/?p=470#primaryimage\"},\"image\":{\"@id\":\"https:\/\/tmainc.net\/?p=470#primaryimage\"},\"thumbnailUrl\":\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\",\"datePublished\":\"2023-08-11T01:10:17+00:00\",\"dateModified\":\"2023-08-11T16:24:09+00:00\",\"author\":{\"@id\":\"https:\/\/tmainc.net\/#\/schema\/person\/acad32c532effdd19cc22f46fdf48f2b\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/tmainc.net\/?p=470\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/tmainc.net\/?p=470#primaryimage\",\"url\":\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\",\"contentUrl\":\"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png\",\"width\":624,\"height\":270},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/tmainc.net\/#website\",\"url\":\"https:\/\/tmainc.net\/\",\"name\":\"TMA Inc.\",\"description\":\"iSeries ERP Support\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/tmainc.net\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/tmainc.net\/#\/schema\/person\/acad32c532effdd19cc22f46fdf48f2b\",\"name\":\"tommanntma\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/tmainc.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c4b570895af95829f469fb812a9191b2?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c4b570895af95829f469fb812a9191b2?s=96&d=mm&r=g\",\"caption\":\"tommanntma\"},\"sameAs\":[\"https:\/\/tmainc.net\",\"https:\/\/x.com\/tommanntma\"],\"url\":\"https:\/\/tmainc.net\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/tmainc.net\/?p=470","og_locale":"en_US","og_type":"article","og_title":"Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.","og_description":"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.&nbsp; After much digging and searching, I was able to find the problems in the XML &hellip; Correct IBM Data Provider Mapping Files for SSMS Import Wizard Read More &raquo;","og_url":"https:\/\/tmainc.net\/?p=470","og_site_name":"TMA Inc.","article_published_time":"2023-08-11T01:10:17+00:00","article_modified_time":"2023-08-11T16:24:09+00:00","og_image":[{"url":"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png"}],"author":"tommanntma","twitter_card":"summary_large_image","twitter_creator":"@tommanntma","twitter_misc":{"Written by":"tommanntma","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/tmainc.net\/?p=470","url":"https:\/\/tmainc.net\/?p=470","name":"Correct IBM Data Provider Mapping Files for SSMS Import Wizard - TMA Inc.","isPartOf":{"@id":"https:\/\/tmainc.net\/#website"},"primaryImageOfPage":{"@id":"https:\/\/tmainc.net\/?p=470#primaryimage"},"image":{"@id":"https:\/\/tmainc.net\/?p=470#primaryimage"},"thumbnailUrl":"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png","datePublished":"2023-08-11T01:10:17+00:00","dateModified":"2023-08-11T16:24:09+00:00","author":{"@id":"https:\/\/tmainc.net\/#\/schema\/person\/acad32c532effdd19cc22f46fdf48f2b"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/tmainc.net\/?p=470"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/tmainc.net\/?p=470#primaryimage","url":"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png","contentUrl":"https:\/\/tmainc.net\/wp-content\/uploads\/2023\/08\/image-26.png","width":624,"height":270},{"@type":"WebSite","@id":"https:\/\/tmainc.net\/#website","url":"https:\/\/tmainc.net\/","name":"TMA Inc.","description":"iSeries ERP Support","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/tmainc.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/tmainc.net\/#\/schema\/person\/acad32c532effdd19cc22f46fdf48f2b","name":"tommanntma","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/tmainc.net\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c4b570895af95829f469fb812a9191b2?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c4b570895af95829f469fb812a9191b2?s=96&d=mm&r=g","caption":"tommanntma"},"sameAs":["https:\/\/tmainc.net","https:\/\/x.com\/tommanntma"],"url":"https:\/\/tmainc.net\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/posts\/470"}],"collection":[{"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tmainc.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=470"}],"version-history":[{"count":4,"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/posts\/470\/revisions"}],"predecessor-version":[{"id":492,"href":"https:\/\/tmainc.net\/index.php?rest_route=\/wp\/v2\/posts\/470\/revisions\/492"}],"wp:attachment":[{"href":"https:\/\/tmainc.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tmainc.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tmainc.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}