I'm attempting to export a sizable Access .mdb database for an SQL Server database and also have been encountering an issue where Microsoft DTS doesn't recognise the information kind of a specific kind of area within the access database.
I've reviewed the access tables under consideration and they're setup as 'text' having a period of 1. They have a single Y or N value if populated but could in addition have a null value.
I've been testing on one table that consists of a area of the type. After I open the 'Edit Mapping' screen the information type is placed to -1 and so i by hand place it to a kind of char having a period of 1 and try to process the table. This creates 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've been reading through various blogs also it appears as though I have to edit the xml mapping files to inform DTS what data type 130 ought to be and so i edited the file c:Program FilesMicrosoft SQL Server100DTSMappingFilesJetToSSIS.xml and went it again but this made no difference.
I added this the xml mapping file after which restarted this program and attempted 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 truth that I acquired the identical error as before brought me to think that editing another mapping files wouldnt really make a difference.
Anybody any ideas?
You might be onto bigger and error messages right now, however i experienced exactly the same problem when attempting to import b .mdb into SQL 2008 R2 while using import wizard. Several fields which were setup as text within the mdb file were tossing the "source data type 130 wasn't based in the mapping file" error. I monitored it lower to text area length within the mdb file. Any text area which was set having a size more compact than 30 was tossing the mistake. Within the mdb file, I elevated the area size all text fields to a minimum of 30, after which I could import the database. Hope this can help!
You have to edit 3 files:
Copy any kind of text and reemplaze the origin for 130 and destination ntext. Works ideal for me.