Reusable Packages

Some developers are familiar with the concept of object-oriented programming (OOP). In OOP, you break up large pieces of a program into its smallest unit of work. So, instead of having one big class file that performed every function in a program, you would, for example, have a class that handled data access. Then, you could refer to this object from any other program, rather than having to re-create the logic over and over again.

In SSIS, you use the concept of parent-and-children packages to perform a similar function, although it’s not quite as elegant as OOP. The concept of parent-and-children packages is where you would break up a large SSIS package into smaller packages based on units of work, and then have a parent package tie the children packages together. How large the unit of work in the child package should be is based on a manageability compromise. Oftentimes, SSIS developers will create a package for each table they want to load; at other times they will create a package per subject area. A favorite way to componentize the package is to have one set of packages for the extract and transformation of the data, and another for the final load of data into the final destination. This way, you can stage your data throughout the day without affecting production. Then, when your production window allows, run the load package to move the data into the production database.

As discussed in the previous section, modularization also helps with development and maintenance. For example, if the entire solution were encapsulated in one single package, then only one developer could work on it at a time for fear of overwriting another developer’s concurrent changes. On the other hand, if the solution was broken into more modular packages, then multiple developers could work on different packages at the same time, thus improving productivity and isolating regressions.

When maintaining packages or fixing bugs, a modular approach will lend itself toward being able to replace just the problematic areas, as opposed to the whole solution. If you compare this approach to compiled programming languages; this is similar to fixing just a specific library component, rather than replacing the whole program every time you fix a bug.

The reason why some people break packages up by a subject area or by an individual table load is so they can run the table load individually in production. Keep in mind that after you deploy a non-modular package to production, you will not be able to run pieces of the package unless you build the package to be dynamic this way, or re-open them in BIDS from your development machine. If you were to break the package up by table, then you could load each table by itself if one piece of the package were to fail. Of course, checkpoints, discussed in Chapter 7, could also help with this.

In the parent package, you can execute the package by using the Execute Package Task and try to run as much as you can in parallel. The children packages can then read the variables from the parent packages by using the configuration feature of SSIS. This feature is discussed later in this chapter. A typical solution would actually have multiple levels of packages calling a sub-package, in a hierarchy several levels deep.