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

added to the list of script references, and the result would be that the behaviors defined
in the assembly would be accessible from within the instance of the Script task or
Script component.
There are several upsides to this approach. First of all, it's a more modular way of
handling code reuse within your package. Rather than relying on rudimentary copy/
paste operations, this method permits a single point of administration and development
for the shared custom functions of your ETL processes. Since all references to the cus-
tom behavior would point to a single assembly on each development machine or server,
any updates to the code would be addressed at the machine level rather than having to
touch every script in every package. In addition, the behaviors built into the external
assemblies could be used by other processes or applications; because these standalone
assemblies are built using the Common Language Runtime (CLR), their use could be
leveraged beyond the borders of SSIS.
There are a few limitations to this approach. First, you cannot use SSDT to create
custom assemblies. Although both tools use the Visual Studio shell, they are only in-
stalled with the templates to create business intelligence projects and do not natively
support other project types. To create an assembly containing custom code, you'd need
to use a version of Visual Studio that was configured to generate class library projects
(the Standard and Professional versions, or even the language-specific free Express
versions)—or, for highly experienced developers, plain old Notepad and the .NET
compiler. Another limitation is that any assemblies referenced in your script must be
deployed to and registered in the Global Assembly Cache, or GAC, on the machine(s)
that will execute the package. This deployment and registration process is not complex,
but it does add to the total of moving parts in your ETL infrastructure.
Custom Tasks/Components
At the top of the SSIS reusability food chain you will find the custom task and custom
component. As with a custom assembly, the ability to add your own tasks and compon-
ents to SSIS allows you create highly customized behaviors within SSIS. In addition,
custom tasks and components enable you to create a more customized user interface for
these behaviors, allowing for relatively simple drag-and-drop use in your SSIS pack-
ages. In the interest of brevity, we won't detail the use of custom tasks or custom com-
ponents in this chapter, but it is worth mentioning that if there is script behavior that is
often repeated in many of your packages, it's worth considering converting the Script
task or Script component into a custom tool that can easily integrate into SSDT.