New Blog Series: Parent-Child Architecture in SSIS

This month I’m kicking off a new series of blog posts discussing the topic of parent-child architectures in SQL Server Integration Services.

elephant I still remember the first SSIS package I ever deployed to a production environment.  It was the mid-2000s, and I was working on a large data migration project which would take my then-employer, an acute care hospital, from an old UNIX-based system into a modern SQL Server-based OLTP back end.  The entire solution, which pushed around a few hundred million rows of data, was completely contained in a single SSIS package.  And this thing was HUGE.  When I say huge, I mean that the package metadata alone was 5mb in size.  I had a bunch of duplicate code in there, and when I opened or modified the package, it took sometimes a minute or more to go through the validation for the dozens of different tasks and data flows.  In hindsight, I can admit that it was not a well-designed architecture.

Fast forward about a decade.  Having learned some lessons – the hard way – about ETL architecture, I’ve relied on a completely different way of thinking.  Rather than relying on a few, do-everything SSIS packages, I prefer to break out tasks into smaller units of work.  In using more packages that each do just one thing, I’ve discovered that this architecture is:

  • Easier to understand
  • Simpler to debug
  • Much easier to distribute tasks to multiple developers
  • In some cases, better performing

As part of my role as an independent consultant, I also do quite a bit of training, and in those training sessions the topic of parent-child ETL architecture comes up quite often.  How many packages should I have?  Should we have lots of small SSIS packages, or fewer, larger packages?  This is also a topic on which I find a lot of questions on SQL Server discussion forums as well.

To share my experience on this topic, I’m starting a new series of post discussing parent-child architectures in SSIS.  As part of this series, I will cover:

  • Overview of parent-child architecture in SSIS
  • Parent-child architecture in SSIS 2005 and 2008
  • Parent-child architecture in SSIS 2012
  • Passing configuration values from parent to child package
  • Passing values from child packages back to the parent
  • Error handling and logging in parent-child structures
  • Parent-child architectures in an ETL framework

I’m looking forward to writing this series over the next few months.  As always I look forward to your feedback.

Tagged , . Bookmark the permalink.

3 Responses to New Blog Series: Parent-Child Architecture in SSIS

  1. Mike Todd says:

    Look forward to reading this article TIm. I have similar experience of creating a HUGE SSIS package (in my case for calculating sales commission payments) that was a nightmare to maintain. I ending up re-writing the whole thing and breaking into smaller bite-sized packages. Each package was controlled by a parent control package that would send and receive parameters from its child packages. Coupled with the auditing that I also added, it proved far easier to work out what was happening at any point in time,

    • Tim Mitchell says:

      Mike, that’s a common evolution from what I’ve learned in talking to others. Instinct is to drop everything into just one package, but as one matures as an SSIS developer, the benefits of a multilayer architecture become more clear.

  2. Pingback: Parent-Child SSIS Architecture - Tim Mitchell

Leave a Reply