Convert Access Database to Microsoft DTS SQL - Data Type '130' Not in Mapping File

I am trying to export a large Access.mdb database to a SQL Server database and am encountering a problem when Microsoft DTS does not recognize the data type of a specific field type in the access database.

I looked at the access tables in question, and they are configured as “text” of length 1. They contain one value of Y or N if it is full, but can also have a null value.

I tested a single table containing 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 char with a length of 1 and try to process the table. As a result, you receive 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

, , xml, DTS, 130, c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\JetToSSIS.xml , .

xml, :

<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>

, ​​ , , , .

?

+3
5

, .mdb SQL 2008 R2 . , mdb, " 130 ". mdb. , 30, . mdb 30, . , !

+3

, xml-, , MSSQL, :

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

JetToMSSql8.xml 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>

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>

JetToMSSql *.xml "Short Text" Access nvarchar MSSQL. , NChar Access , , , . JetToSSIS.xml , . , SSIS .

+7

3 :

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

130 ntext. .

+2

130 30 - , Access 2003 . ( 100, 50 - 130, 100) , Access 97. , , , , 16

+1

10 ( DAO 3.6)

  • . DAO 3.6. 130 ADO.

0

All Articles