In an earlier blog post I talked about how my biggest wish for SSIS vNext is that they find some way of letting us build libraries of pre-configured data-flows, tasks and transformations so that we can easily pick them up and drop them into our packages. Any changes to the task/component in the library would be reflected wherever that component is used. In other words a task/component exists in one place and we instantiate that object in our packages...a bit like instantiating class objects in managed code.
It was suggested that this could be achieved through the use of child packages and while I agree that functionally this could be done I had some reservations, mainly around the overhead of launching child packages here there and everywhere for what were essentially simple discrete operations.
I decided to check out what effect this overhead could have and whilst my test was quite simplistic the results were quite startling.
I had a package containing a sequence containing a script task. The script task did nothing more than pop up a msgbox. I had an OnPostExecute event handler that I wanted to use to log a record to a table every time the event was raised. The event would be raised 3 times, once for the script task, once for the sequence container and once for the package.
I compared 2 methods of logging within the event handler. The first was to use an Execute SQL Task to write out to the table and the second was to call a child package containing an identical Execute SQL Task. I ran both methods 3 times.
The first method completed in an average time of 14 seconds. The second method completed in an average time of 37 seconds. Quite a difference I think you'll agree!
There is a caveat to this and that is that BIDS itself is an overhead when launching packages and I shouldn't expect these sorts of results when running in a production environment where BIDS is not an issue.
However, it still seems safe to assume that launching child packages is an overhead that will cause fairly severe time impacts on your overall execution.
The message seems simple. Use child packages where you have to but use them sparingly! Don't be calling out to them for single discrete operations because the overhead of launching the package negates the advantage of having modular code (in my opinion).
I'd be interested in hearing other people's opinions or experiences with using child packages, particularly in regards to performance.
-Jamie
UPDATE 2005/03/02: I neglected to mention that I also had a child package from the package's OnPreExecute event handler. Once I eradicated this and brought the same functionality directly into the event handler my package executed in 3 or 4 seconds.
4 seconds down from 37 seconds just by eradicating child packages. Point emphasized I feel!!
UPDATE 2005/03/08: More on this. As with all good arguments there is always a counter-argument. I did a bit more testing, essentially removing BIDS from the equation, and found some more interesting results. Read about it here.