How to synchronize data between Staging and Production Sql Server Databases on the same server using SSIS Package?

"Both Databases are copy to each other" - so you mean you want to synchronize data from production to staging (which is fine in my opinion) but to sync it from staging to production...that could lead to disastrous results right? Meaning production data gets lost as it is replaced by staging data - right? I would never allow making DML changes to my production databases without a solid rollback script available plus to perform these deployments/changes controlled after a production database was taken and kept safe.

Assuming you still want to synchronize them regardless of all of the above - you could use SQL own Replication featur or CDC (Change Data Capture) can also be a solution but this way you need to build nearly everything on your own, or atabase change tracking. It is a built-in SQL Server function that does for you change tracking including inserts, updates and deletes. Everything else like sending and applying changes, solving conflicts etc. you will have to code yourself.

You can also use some tools like RedGate Data Compare, ApexSQL Data Diff, and dbForge Data Compare, which use the data compare method.or you can use SQL Database Studio, which automatically generates reusable SQL scripts.

Hi,

I have a Admin website which inserts new data/updates the existing and deletes the unnecessary data. This admin data inserts in the Staging Database.  Sql server job runs in the night which moves the data from staging database to production database. Currently this is done by Sql Server Replication. But we need to replace Sql server replication with SSIS Package.

I want SSIS Package moves the newly inserted data, new updates, new deletes from Staging Database to Production Database.

I know how to sync one staging table to production table. Please see the attached picture. How syncing can be done on multiple tables. I just need to sync the daily data.

"I know how to sync one staging table to production table. Please see the attached picture. How syncing can be done on multiple tables. I just need to sync the daily data."

So if you know how to sync one table via the SSIS package all you need is to add one "Data Flow Task" per each table into the same package like you did for that one table already. You'll need to add source - destination, mapping, transformations etc for each of the 12 tables you need to sync.

I am having 12 tables:
1. Which option is good for validation and error handling:
--------Creating one package and having 12 data flow tasks for each table.
--------Creating 12 child packages, each package having one data flow task for one table. Master Package contains 12 child packages.

Problem is tables have relationships, foreign keys. Is it a preferred way to delete the foreign keys in the beginning and create foreign keys at the end.

I need to send an email if total count of source table does not match with total count on the destination table, Check if Max(Primary Key) on Source Table and Max(Primary Key) on the destination table matches, what's the best way to do the validation if syncing data is having no issues.
EachTableDataFlow.jpeg.crdownload
DataFlowTasks.jpeg.crdownload

I believe one package with 12 data flow tasks should be enough as you can control the order of execution and how many SSIS tasks run in paralel - I think default is 4. Considering this your options are:
- to put/process the child tables first then parent tables last so you don't have to drop all FK-eys at the begining and re-create them at the end
OR
- to have first task in the SSIS workflow a SQL task that will drop all FK-eys, process all 12 tables and add the FK-eys WITH CHECK in another SQL Execute task at the end then final task do the counts/compare


Ouh...and make sure to take Production database backup before this and also - make sure to test, test, test before running such sync against a production database.