question

jdanao avatar image
jdanao 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!
ssisbackuprestoretruncate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Shawn_Melton avatar image
Shawn_Melton answered
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/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.