Convert Access Database to SQL Microsoft DTS - Data Type '130' not in mapping file

Question

I am trying to export a large Access .mdb database to an SQL Server database and have been running into a problem where Microsoft DTS does not recognise the data type of a particular type of field in the access database.

I have taken a look at the access tables in question and they are set up as 'text' with a length of 1. They contain a single Y or N value if populated but can also have a null value.

I have been testing on a single table that contains a field of this type. When I open the 'Edit Mapping' screen the data type is set to -1 so I manually set it to a type of char with a length of 1 and attempt to process the table. This produces the following error message:

[Source Information]
Source Location : C:\admin\facdata.mdb
Source Provider : Microsoft.Jet.OLEDB.4.0
Table: `ACASSCATDEPREC`
Column: DepBook
Column Type: 130
SSIS Type: (Type unknown ...)
Mapping file (to SSIS type): c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\JetToSSIS.xml

    [Destination Information]
    Destination Location : SERVERNAME
    Destination Provider : SQLOLEDB
    Table: [dbo].[ACASSCATDEPREC]
    Column: DepBook
    Column Type: char
    SSIS Type: string [DT_STR]
    Mapping file (to SSIS type): c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML
    [Conversion Steps]
    Conversion unknown ...
    SSIS conversion file: c:\Program Files\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

I have been reading various blogs and it seems as if I need to edit the xml mapping files to tell DTS what data type 130 should be so I edited the file c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\JetToSSIS.xml and ran it again but this made no difference.

I added this the xml mapping file and then restarted the program and tried again:

<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>Char</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:CharacterStringType>
            <dtm:DataTypeName>130</dtm:DataTypeName>
            <dtm:Length>1</dtm:Length>
        </dtm:CharacterStringType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>

The fact that I got exactly the same error as before led me to believe that editing the other mapping files wouldnt make a difference.

Anyone any ideas?

Answer 1

You may be onto bigger and better error messages by now, but I encountered the same problem when trying to import a .mdb into SQL 2008 R2 using the import wizard. Several fields that were set up as text in the mdb file were throwing the "source data type 130 was not found in the mapping file" error. I tracked it down to text field length in the mdb file. Any text field that was set with a size smaller than 30 was throwing the error. In the mdb file, I increased the field size of all text fields to at least 30, and then I was able to import the database.

Answer 2

To elaborate on this, if you choose to go the xml route, the files you will need to edit for an Access to MSSQL are as follows:

%ProgramFiles%\Microsoft SQL Server[Your Version]\DTS\MappingFiles\

Add the following to JetToMSSql8.xml and JetToMSSql9.xml

<!-- 130 -->
<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>130</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:CharacterStringType>
            <dtm:DataTypeName>nvarchar</dtm:DataTypeName>
            <dtm:UseSourceLength/>
        </dtm:CharacterStringType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>

And to JetToSSIS.xml

<!-- 130 -->
<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>130</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:CharacterStringType>
            <dtm:DataTypeName>DT_WSTR</dtm:DataTypeName>
            <dtm:UseSourceLength/>
        </dtm:CharacterStringType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>

The JetToMSSql*.xml will assist with mapping these "Short Text" fields in Access to the nvarchar datatype in MSSQL. I'm under the impression they're actually stored as NChar in Access internally, but for most purposes the variable solution is likely fine. The JetToSSIS.xml then maps the data type to wide string, as you'd expect. With these files updated, SSIS wizards will treat such columns normally.

Answer 3

The answer to the 130 problem for me was not about field lengths of 30 or more - it is the fact that you CHANGE the field length in Access 2003 or greater. (I changed mine to 100 leaving some with lengths of 50 alone - these continued to error 130 - so I changed them all to 100) I think my problem stemmed from copying a couple of tables from an Access 97 database. I have hundreds of fields in other tables which gave no problems even though they may have been a length of 16

Answer 4

You need to edit 3 files:

• IBMDB2ToSSIS10.xml
• JetToSSIS.xml
• DtwTypeConversion.xml

Copy any type of text and replace the source for 130 and destination ntext. Works perfect for me.

Answer 5

I imported data using the SQL Server Import and Export Wizard from SSMS and faced with the same issue. I tried @Avarkx solution, but without success. But then I realized that SSMS itself has its own versions of JetToMSSql8.xml, JetToMSSql9.xml and JetToSSIS.xml files which are located in [SSMS_Install_Path]\Common7\IDE\CommonExtensions\Microsoft\SSIS[SQL Server version number]\MappingFiles When I applied @Avarkx solution to these files, it started to work without errors.

Answer 6

The field that have this problem, have type 10 (text in DAO 3.6) and attribute

1. The attribute should be
2. See the properties of fields with DAO 3.6. The type 130 refers to ADO.