Let's say I have three servers, ServerA, ServerB, and ServerC. ServerA has the production database, ServerB has the staging database, and ServerC has the data warehouse. I've created an SSIS package to transfer data from ServerA to the staging database on ServerB (Stage 1), then from the staging database to the data warehouse on ServerC (Stage 2). Would it make any sense to have separate SSIS packages for each stage? Is there network traffic between ServerA and the other two servers during Stage 2 because that's where the job is executed from?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Yes there will be traffic to Server A during stage 2. This is because the data that has been staged will need to come back to the place that is doing the work (Server A) so that the package can send the data to the target (Server C).
I think you may be doing this the wrong way around to be honest. If Server A has the production database on it then you want to put as little extra load on it as possible. Because of this I would suggest placing your SSIS package on Server B which then only has to pull data from Server A - meaning that there will be no additional processing load on Server A other than satisfying the query that is asked of it.
Then, once the staging has been complete stage 2 can begin which will only consume resources on Server B and Server C. This is a much better approach - I don't see any need to have multiple packages unless you want to split the logic of each stage into their own modules so that they can be maintained separate from one another.
The other benefit to this approach is that you can make alterations to the package and the staging database without having to change anything on your production server - this decoupling allows better separation of duties and will make future maintenance easier for you.
I hope this helps you.