We're planning to use a data mapping tool like Altova MapForce to create a new database with data for analysis and reporting. The idea is to take our legacy databases and other storage's and create a new clean database that is updated nightly with fresh data.
I would like to use DO as a means to retrieve data from that new database. For as far as I know there is no library to reverse engineer databases for DO at the moment so I came up with the idea to generate the model from the metadata in the DB using SQL-SMO and T4.
Before embarking on that journey I need to know some things:
I think I can generated the code for the model from the metadata (using T4 and SQL-SMO) pretty easily. I already have a prototype working that retrieves the DB metadata. Then after having the model compiled I need to access that DB using DO. Normally DO would need metadata in the DB to be able to compare the .Net model with the DB model. Do I also need to generate that information (that is probably going to be a hell of job) or is there another way to achieve that?
If you have other suggestions on how I could achieve the above I would be greatfull.
If your new database is empty (does not contains any tables) then you do not need to create metadata for DO. You should use Perform or PerformSafely upgrade modes. DO will create tables according your generated model (if model is correct) and metadata in new database on first domain building.
NOTE that, if you will need to do some changes (except additions of new types or columns) in model then in PerformSafely mode you will must generate hints for this changes too.