asked
Best way to refresh an entire database daily?
BACKGROUND: I have a production database with a total of 273 tables - about 40 of which are replicated on another database that is then used as the source for performing data warehouse extractions & transformations. I have an SSIS package that is executed daily via SQL Server Agent job, and this package literally has 40 individual pairings of an SQL task + data flow task that truncates the replicated table and re-loads from production, respectively. QUESTION: I have a new requirement in which I need to add the remaining tables that are not currently in my SSIS package. I'd rather not create the remaining 233 pairings! There's gotta be an easier way to do this (right?!), and I'm seeking feedback on my options. Thanks in advance!
I would suggest looking into Biml, you can find a good bit of information here on [
bimlscript.com][1]. This is basically dynamicly building SSIS packages using readable XML. It will sit there and build all those 300 pairings you need to truncate and load the data...obviously as you tell it. There is a few videos out there on YouTube from Variagence and think a few Virtual Chapters with PASS as well. Your other option if you need the whole database is just restore the database from the last backup. It does not sound like your database is that large so would expect it would only take a few minutes to restore that database. This can be easily scripted out or done in SSIS. [1]:
http://bimlscript.com/
Definitely go with a backup if at all possible. So much less stuff to maintain. The ETL is enough to worry about without all these steps as well!
18 People are following this question.
