Scripting Patterns - SQL Server Integration Services Design Patterns - page 112

To that end, an integral part of the solution design process should be evaluating the
flexibility, maintainability, and reusability of the project as a whole, making specific al-
lowances for the project-within-a-project instances of the script.
Code Reuse
Laziness is a good thing. (Pause for effect.) To clarify: all great technologists find ways
to avoid solving the same problems repeatedly. Let's say you've spent some time work-
ing with script tasks and script components, and you come up with a whiz-bang idea
for The Next Big ETL Thing. The Thing is so narrowly focused that it adds behavior
not present in native SSIS tools, but it's also versatile enough to be used in multiple
packages across several domains. Since you worked so hard on it once, you'll want to
avoid reinventing The Thing again. The solution: find a way to make The Thing reus-
able.
To that end, there are several ways to reuse code within SSIS, from the old-fash-
ioned copy/paste to fancy modularization.
Copy/Paste
No further definition is needed here: code reuse via copy/paste is exactly as it sounds.
Although copying and pasting code as a reuse mechanism is a bit crude and unstruc-
tured, it's also the simplest and most commonly used means to do so within SSIS. The
upside is a quick and easy deployment with few administrative limitations. However,
this simplicity comes at a cost. Deployed in this manner, each copy of the code exists
in its own silo and must be independently and manually maintained.
External Assemblies
As I mentioned earlier in the chapter, both the Script task and the Script component al-
low you to reference external assemblies (compiled code generated from a separate
project) to import supplemental behavior into the instance of the Script task/compon-
ent. The details of creating an external assembly are beyond the scope of this chapter,
but in a nutshell, you would use a separate development environment to code and com-
pile the classes, methods, and events to be used in your ETL processes. The resulting
binary file, known as an assembly , would be deployed to the development machine and
any server machine(s) that would execute the package. The assembly would then be