I have an old AS/400 system that's going offline on Wednesday, and I've been trying to import all of the data into a SQL Server database for backup purposes for about a week now, but I'm having trouble doing so.
The SQL Server has IBM System i Access for Windows installed, as well as the DB2OLEDB (Microsoft OLE DB Provider for DB2) installed. I have tried using both of these providers with the SQL Server Import and Export wizard, and while I have successfully created the connection and found the data I'm looking for, neither provider can seem to handle the data conversion. The DB2OLEDB will automatically locate any DECIMAL based fields, but that's about it. It cannot automatically find and convert CHARACTER NOT NULL fields, which is the majority of the fields. Here is the error I'm getting when I try to import:
Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.
The most common column error:
- Column "[column]": Source data type "CHARACTER" was not found in the data type mapping file.
There are THOUSANDS of tables in the AS/400, so manually scripting this out is absolutely not an option.
Is there an easier way? Is there a setting somewhere I'm missing?
After a lot of research, I just couldn't get anything to work the way I needed. I ended up using the iAccess tools to just back it all up into Excel files. Took forever, but it got done.
Thanks for the help.