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?


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.

  • It does help, thanks. I was assuming I would put the staging database on the production server to minimize the amount of time it takes to get the changed data out, but I guess if my network connection between the two servers is good I can just put the staging database on a different server. – influent Sep 19 '12 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.