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

mathematical operations, and structured error handling, among many others—as part of
their scripts. Need to create a complex multidimensional array? Looking for an easier
way to perform tricks with string manipulation? All this and more is easily accessible,
just below the covers of the .NET runtime environment.
Compiler
Finding errors in code is critical and is generally easier to do early in the development
process. To understand how this works in SSIS scripting, it's useful to understand the
life cycle of script development.
When SSIS first surfaced with SQL Server 2005, the ETL developer could choose
to store the code text in a Script task or Script component without actually building
(compiling) the script project. By opting not to precompile the script, a trivial amount
of processing resources would be saved (or more specifically, delayed) by forcing the
SSIS design environment to accept the code as written. Two problems arose from this
behavior: first, there was a performance penalty, however slight, caused by compiling
the script on the fly during the execution of the package; second, the risk of runtime er-
rors increased, due to the minimized up-front validation in the designer.
Starting with SQL Server 2008, script precompilation is required. Now when you
write a script, it is compiled in the background and the resulting binary output is serial-
ized and stored inline within the package XML. As soon as you modify your script and
close the editor, the .NET compiler is invoked and creates the serialized binary data. If
you've made an error in your code that prevents compilation, you'll be presented with
a validation error on the Script task or Script component indicating that the binary code
cannot be found (see Figure 3-3 ).
Figure 3-3 . Compilation error with a Script task
This validation step is a safety mechanism to make sure your packages don't make
it to deployment with uncompilable code. However, it's also possible to build the script