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

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.