My last post on using child packages in SSIS solutions had been bugging me a little. In it I was basically making the conclusion that using child packages is generally a bad thing if you are going to be calling them multiple times. What I didn't consider when making this rather hasty judgement was the overhead that BIDS places on the execution and was THIS the main reason that my packages were running so slowly.
So...onto a little investigation.
I put together a package structure that called multiple child packages, very similar to what I used to demo my last post. I ran it through BIDS and the execution time was approx 90 seconds. I ran it using the command line tool DTExec and the execution time was sub-20 seconds. I don't think I really need to say much else about this.
Clearly BIDS places a massive overhead on running your packages. This isn't surprising when you consider everything extra that is going on when BIDS is used. Showing all of the running process...showing progress...consuming events, colouring everything in pretty colours....it all comes at a cost. A heavier cost than certainly I had previously imagined.
I would still advocate not using child packages for doing one-off discrete operations (e.g. inserting into a logging table) but it seems that the overhead of using child packages is not as great as I had previously assumed. The overhead is with BIDS....and a big overhead it is too.
The lesson here seems to be that if you want to ge a true representation of how your package is going to perform in a scheduled, live environment....run it using DTExec, NOT through BIDS. [Another personal lesson is not to go jumping to assumptions and conclusions but we'll leave that to one side for now :) ]
-Jamie