What is “good” SSIS? What is best practice for SSIS packages? How do I make my SSIS better? These are all questions I hear a lot. The answer is never simple but usually include some mention of naming conventions (I have seen too many packages where tasks, sources, destinations and connections are using default names), the correct use of sequence containers, the use of fast load parameters to setup your dataflow tasks for optimal performance in your environment, and in quite a few cases, breaking a large, complicated SSIS package into smaller packages.
So why break a package up into smaller packages? It simplifies debugging and maintenance, and in many cases aids performance. Ever find yourself waiting a long time for the package to start doing actual work? Consider smaller packages. There are a few limitations and issues with doing this. The obvious issue is configuration, you are no longer setting up connections in just one package – you now have to set them up in several packages. Add this to a good infrastructure of a development, testing and production environment and suddenly it becomes a big problem. Now surely this can be overcome by using configuration files? This in itself poses a few problems. Do you create a configuration file for each package? Do you have all your connections in every package, regardless of whether you use them or not? Do you configure the connections in the job steps?
So far the problems in breaking up your SSIS package can be overcome. But what other limitations are there in doing this? I often have information that needs to be passed between tasks. This means I now have to split my packages based on dependencies rather than functionality. Breaking up the package based on functionality does make much more sense though, mainly because this would make the small packages reusable for other projects. The standard way for calling these smaller packages is where the limitation lies. The standard execute package task in SSIS allows you to pass variable values into a package by using the configuration settings of the package. It does not allow you to pass values back.
By being able to pass values into a package and returning values, you will be able to refactor your SSIS, making your SSIS packages reusable.
So back to the original question – what is “good” SSIS? “Good” SSIS is like good code. Good SSIS should be maintainable, reusable and optimal.
With these requirements in mind I decided to do my bit for SSIS. I created an enhanced execute package task that allows you to pass variables into the package, return variables from as well as pass database connection information. For more information on this go to http://ssisexec.codeplex.com
-EDIT- I have rebuilt the enhanced execute package task for SSIS 2008 R2 