5/14/2012 6:48:34 AM
Four years ago I raised a Connect item called "SSIS Package Parts" (https://connect.microsoft.com/SQLServer/feedback/details/338609/ssis-package-parts#details - 50 "up" votes so far) where I requested the ability to store a subset of a package's control-flow as a file and *instantiate* it inside of a .dtsx file. I colloquially refer to these files that contain subsets of functionality as "package parts"
The value that this would bring to SSIS development can be summed up in one word - reusability. For example, I have an Execute SQL Task that I have copied and pasted into multiple packages simply because it does the same thing in each - e.g. INSERT the value of a variable into a table. Clearly this is not an optimum way of working because we have the same code in multiple places, thus negating the value of DRY (i.e. Don't Repeat Yourself).
The answer from the SSIS team as to why this has not been implemented is that the same can be achieved by putting the Execute SQL Task into a .dtsx file all of its own and calling it from the Execute Package Task. I have pretty much accepted this reasoning but have recently had a change of heart - I no longer consider this to be a satisfactory workaround for the following reasons:
1) Siphoning functionality into a dedicated package introduces an extra executable in the executable hierarchy. Not only does this consume extra resources (because it is a package) but it also results in superfluous log messages to be recorded in tables:
in the SSIS Catalog. Moreover, it also results in superfluous executables to be listed in -[event_messages].[execution_path]
All of this extra superfluous information makes the log information more cluttered and therefore harder to derive any value from the logs.
2) Siphoning functionality into a dedicated package also has the disadvantage that information may not be logged against the relevant package. For example, suppose I have a package called "InfoLogger.dtsx" that logs throws an information event containing some value that is passed to it via a parameter from another package (let's call that package "DoWork.dtsx"). If I look in [catalog].[event_messages].[package_name] then I will see "InfoLogger.dtsx" whereas in reality the information is pertinent to "DoWork.dtsx". This means that I can no longer filter on [package_name] to get all the information pertinent to that package. Package Parts would prevent this.
P.S. This technique of having a dedicated logging package (InfoLogger.dtsx) is something I've been doing a lot recently as it adheres to the DRY principle (http://en.wikipedia.org/wiki/Don't_repeat_yourself) - something I am a big fan of.
3) The executED (i.e. child) packages cannot write to variables in the executING (parent) package. This is a big BIG drawback. Variables are the means by which executables communicate between themselves so in forcing us to hive off functionality into other packages we lose that "line of communication" (and I don't accept that using a Script Task to write to variables in the parent package is a suitable workaround).
Think about an Execute SQL Task that executes a stored procedure with an OUTPUT parameter. If we're calling that stored procedure from a dedicated .dtsx package how do we ever get to use the OUTPUTted value? Simple answer - we can't.
I really hope that this request earns some serious consideration. Its a failing within SSIS that has been unaddressed for too long in my opinion.
N.B. If you vote this up (or down) please leave a comment explaining why. The product team need to see good reasons why this should be implemented.