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

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

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?

Solution

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.