I have ms access db which has complex relationship between tables, and there are above 100 tables in the database, the relation between tables are very much complex ( I mean in terms of referential integrity ).
I would like to migrate ms access tables to sql server with referential integrity intact.
For this I tried MS access 2010 upsizing functionality which fails with below errrors
Relationship or Constraint Failed to Upsize: Server Error 1753: Column 'tblFinInstructions.FinInsId' is not the same length or scale as referencing column 'tblAddFinInst.FinInstId' in foreign key 'tblAddFinInst_FK00'. Columns participating in a foreign key relationship must be defined with the same length and scale. Server Error 1750: Could not create constraint or index. See previous errors.
I have above errors at lots of places in the above activity which is being generated as report at the end of upsizing fuctionality.
I tried Sql server migration assistant it ends up with lots of warning and one error message but again it does only partially migration of referential integrity to sql server tables.
I also tried SQL server import and export functionality which also has done partial moving referntial integrity of ms access tables to sql server.
Above stuff do not seems to be working for my database atleast. and I am middle of sea now, if someone can please help if one has done similar activity in past with success would be very much helpful.