I need to do a nightly refresh of the data in a test database from production. Can I do this using SSIS? Because of referential integrity, the destination tables must be deleted in a certain order and then loaded with production data in the opposite order. I just need some guidance as the best way to approach this.
We use a script for that:
Delete rows from Destination which no longer exist on Source (in reverse FKey order, Children first)
UPDATE rows that exist on both, but which are different (in ANY column, using a BINARY COLLATION on String datatypes so that Upper/Lower case and accent etc. difference are detected, or if one is NULL and the other not)
INSERT rows that do not exist on DESTINATION
UPDATE and INSERT happen table-by-table (rather than updates to ALL tables first), and are in Forward FKey order, (Parent first)
Even this is not infallible as an UPDATE may be for data that is circular ...
Easier way, perhaps?, is to drop all FKeys first, do the UpSert, then recreate the FKeys.
We ONLY freshen up rows that are different in at least one column - the LOG activity if we deleted all rows and re-inserted them all, every night, would be a killer for us (our DEV databases run in Full Recovery Model - we are far more likely to need to recover to point-in-time in DEV than the Production systems - lots more Human Error in DEV!!)
P.S. We mechanically generate the script that does this, so we can easily generate a script for a new database that we want to periodically copy data from DatabaseA to DatabaseB. Also, we can regenerate the script if the DDL of the database changes
Sure you can use SSIS for that. It's an easy way to do it and should perform very well since it exploits parallelism where it can.
I don't use SSIS, so I'm ignorant about it. Does it have an inbuilt "foreign key order" so that it will insert parents before children and things like that?
Just curious, 'coz that would be a handy tool.
No built-in dependency checker for FKs, no. You need to do the tables in order. Pretty easy really, especially in a DW where the FKs are usually not more than one level deep but not hard in for a highly-normalized db either, once you understand the dependencies.
1 Like
Could you just restore the db instead, and then adjust the users / security accordingly? That's typically what I do here, as the data is far too large to try to restore/copy table by table.