August 11, 2011 at 7:10 am
Hi,
I need some suggestions on the SSIS package i will be creating in future.
I have to migrate some historical data as well as current data from one DB schema to another DB schema.
The data is related to hospital. There is a 10 years of historical data which needs to be migrated based on certain rules. Also the current data(from 2010) will be migrated with a scheduled job based on same rules as for history. There are in all 5 tables in Target DB and 6 tables in source Db. To migrate data based on rules , SQL queries have been created.
Now i want to include this in a transaction. I want to commit data in transaction per year per hospital.
There are 5 queries created for data insertion in 5 tables . So shall i include all these queries in a single excute sql task and set transaction property Required for it. How can i commit data based on per year per hospital since there is 10 years of data with more than 50 hospitals.
Can anybody give me any idea. If you need any more info then let me know.
Thanks in advance
Aspg
August 12, 2011 at 11:17 am
how to use transactions?
I personally use this method, it works and it's controlled well, I never had any luck with SSIS transactions
So shall i include all these queries in a single execute sql task and set transaction property Required for it?
it depends on, if you need the 5 of them to be rolled back if there's an error or not!
How can i commit data based on per year per hospital since there is 10 years of data with more than 50 hospitals?
it depends on how the 50 hospital's data are stored and how do you want to load them.
I believe that's a one time load, so tweak your query to insert a year by year, in your WHERE statement, and change it manually
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply