February 25, 2018 at 9:37 am
I have 40 tables having different structure in one of DB on one server that is being updated by data provider.
I want to create a SSIS package that would pull data from that data provider DB and insert ,update or delete (merge) data in to development ,Test,UAT and prod DBs.
The tables are having 1m- 3m rows and 20-30 columns each and all the DBs are on SQL Server platform and are on different servers.
The business requirement is to load data everyday on a particular time and have to use SSIS for this. I am new to SSIS and want your suggestions to create better design.
February 26, 2018 at 2:11 pm
It looks like full copy from prod to lower environments on daily basis. If so, why not backup and restore? Or something like SQL Clone?
GASQL.com - Focus on Database and Cloud
February 26, 2018 at 3:36 pm
Why use SSIS?
It's one of the worst possible choices.
Unless it's some kind of test question.
But then it's for you to answer.
_____________
Code for TallyGenerator
February 26, 2018 at 4:54 pm
Alexander Zhang - Monday, February 26, 2018 2:11 PMIt looks like full copy from prod to lower environments on daily basis. If so, why not backup and restore? Or something like SQL Clone?
Trouble with backup and restore everyday is the security, logins and users need to be mapped/recreated each time. SSIS is brutal if you are moving one table at a time in a data flow task. However it does have two tasks that may be of interest to you.
1. Transfer database
2. Transfer logins (this basically addresses the issue with the prior logins on the new server being nullified).
Using these in tandem may be what you want. Its been a while since I touched on this, so this just to give you some clue.
----------------------------------------------------
February 27, 2018 at 3:06 pm
MMartin1 - Monday, February 26, 2018 4:54 PMAlexander Zhang - Monday, February 26, 2018 2:11 PMIt looks like full copy from prod to lower environments on daily basis. If so, why not backup and restore? Or something like SQL Clone?Trouble with backup and restore everyday is the security, logins and users need to be mapped/recreated each time. SSIS is brutal if you are moving one table at a time in a data flow task. However it does have two tasks that may be of interest to you.
1. Transfer database
2. Transfer logins (this basically addresses the issue with the prior logins on the new server being nullified).Using these in tandem may be what you want. Its been a while since I touched on this, so this just to give you some clue.
Yes, we need to re-create users(looks no need to touch login) after restoring DB. But I will still consider to do that if a full copy on daily basis is needed (especially if tables are huge). Here is my thoughts on it.
1. Backup&Recreate users sounds too much. Actually, it's not so hard. There is much similar scripts online FYI. And the script looks very possible to be reused in other projects.
2. If tables are huge, restore might be fastest way. Plus, we probably get additional advantage via backup compression. And it's possible to use the existing backup file (assume there is regular backup job in source). It means, no need to worry about the load of SSIS in source.
GASQL.com - Focus on Database and Cloud
February 27, 2018 at 7:19 pm
Fastest way would be to track the changes at the source (say, by recording pk values for inserted/deleted/updated records in "change" tables) and then copy only changed records over.
Unless you practice bulk "refresh" of the data at the source.
And this case all the records will be recorded as "changed" anyway, and backup-restore would be the fastest way of data synchronisation.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply