Link to home
Start Free TrialLog in
Avatar of Saroj13
Saroj13

asked on

How to sync data from Staging Database (multiple) tables to Production Database (multiple) tables using SSIS Package?

How to sync data from Staging Database tables to Production Database tables using SSIS Package? I need to use SSIS Package only, Sql Server agent job runs daily to sync the data from Staging Database tables to Production Database tables
Staging Databases:
Tables:
GroupType:      Columns: GroupID(Primary Key), GroupName
ItemCategory:  Columns: ItemCategoryID(Primary Key), ItemCategoryName, GroupID(Foreign Key)
Item:                  Columns: ItemID(Primary Key), ItemCategoryID(Foreign Key), ItemName,EntryDate
ItemTemplate:  Columns: ItemTemplateID(PK), ItemID(FK),ItemTemplateName, ItemTemplateDescr
ItemDescription:Columns: ItemDescriptionID(PK), ItemCategoryID(FK), ItemID(FK), CreateDate


Production Databases:
Tables:
GroupType:      Columns: GroupID(Primary Key), GroupName
ItemCategory:  Columns: ItemCategoryID(Primary Key), ItemCategoryName, GroupID(Foreign Key)
Item:                  Columns: ItemID(Primary Key), ItemCategoryID(Foreign Key), ItemName,EntryDate
ItemTemplate:  Columns: ItemTemplateID(PK), ItemID(FK),ItemTemplateName, ItemTemplateDescr
ItemDescription:Columns: ItemDescriptionID(PK), ItemCategoryID(FK), ItemID(FK), CreateDate

Both Databases have same schema. How to sync data efficiently from Staging Db to Production DB. Tables have dependencies.

Please find the attached file for syncing one table data from staging DB to Production DB. How to sync multiple tables from staging Db to Production DB?
Avatar of lcohan
lcohan
Flag of Canada image

If I understand correctly and the "sync" is only unidirectional from Staging to Prod and these are some lookup tables where production data can be completely wiped out and refreshed from Staging then I would do the following in a SSIS:

have one TSQL Execute task to delete the all data at destination database - child first then parent
add one data flow task per table and populate the Production tables in order to not violate FK-eys and this can be done by setting appropriate precedence and how many SSIS tasks are executed concurrently - in parallel.

Alternatively you can have a task that drops all FK-eys at the beginning populate all destination tables by delete all at destination - insert fresh from source and last have another tsql execute task to add them WITH CHECK at the end.

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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.