We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly.
During this time, we restrict access to the application, this way the database is not altered during integration.
1/ can integration be done on Snapshot of the database, so that we can keep the app up and running?
2/ general question: how to handle the period when datawarehouse is rebuilt? Access to report should be forbidden until all tables are refilled with fresh data. Does exist a mecanism of balance between 2 datawarehouses ? It could be done with parameterised datasources in SSRS instead of shared datasource, but it seems to me like gasworks.
Environment: SQL Server 2005 and we plan to migrate to 2012.