SQL Server
As I have mentioned in a previous blog entry, SSIS provides some great features for modularization for efficiency and ease of maintenance. One such feature is the Package Execution task. This allows us to create a Parent-Child package architecture. Again, the name of the game is reuse. In the same way as we code Stored Procedures to call other Stored Procedures in a modular architecture, the best practice in SSIS is to create a Parent or “master” package that executes child packages. It is all too tempting to create one package that does everything but we must resist this temptation and try to modularize. If we don’t, we quickly end up with a complex package that is difficult to understand let alone to test and maintain. To create a Parent package, just drag and drop the Execute Package task from the Toolbox in the SSIS Designer then provide the name and location of the child package to be executed. Just like other tasks, you can include the Execute Package task in a container such as a ForLoop container for iterative processing. To make the execution dynamic, we can pass a variable to the child package. First of all, create a variable in the Parent Package then create the equivalent variable in the Child Package. In the Child Package, use the Package Configuration Wizard to create a “Parent package variable” configuration. The wizard will walk you through mapping the Parent Package variable to the Child Package variable – known as the “Target Object”. As always with variables, it is up to you to map the variable to the property you want to be dynamic using Property Expressions in the Child Package. At the end of the day, you will end up with a parent that communicates well with its child in a dynamic and effective way. Now that’s what I call responsible parenting. Now if only I can limit the child from sending text messages… Cheers Brian
