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>
, , , , .
?