importing from access error

Let me describe my situation

I am running SQL Server 2005. I created all the tables/columns/keys I needed. I export the data to Access.

In Access, I add all the data for the tables. Then I use SSIS to import-append the data back in to SQL Server 2005.

I get this error:
-
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - tbl_name" (19) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
--

I can use the import tool (SSIS) to create new tables, but not append the data. Creating new tables makes all the tables to lose information about field lengths etc, so this is not an option

any help would be greatly appreciated.

Thanks!

Could you verify the metadata of your existing tables matches the tables you want to import. The error message hints a problem in that area.

Thanks.

|||yea..turns out the metadata was in error...but the access DB's metadata is the result of an export of the SQL Server DB.

Why doesn't Microsoft let you import what you export? just seems to make sense to me.
|||

It is because the data type spectrum is much richer for SQL Server than for Access. By transferring data from SQL Server to Access you are basically downgrading richness of your data and it makes the reverse process difficult without the user intervention.

Even for equally rich data type spectrums of heterogeneous data sources you will end up having to map more than one type to the single destination type. When going in the opposite direction you will have to pick one of possible types as a best match, and the best match will not always be the proper one.

HTH.