First published on MSDN on Jun 17, 2009
A recent discussion with MVP
Andy Leonard
about a new property which showed up on the Script Task in SQL Server 2005 SP3 reminded me that I’ve been meaning to blog about how the script task and script component cache their precompiled code at runtime. This behavior has changed in every SP since 2005 RTM, and while there are
a couple
of
KB articles
that describe the issues that prompted the changes, I haven’t seen anyone document the behavior itself.
Note:
The behavior in SQL Server 2008 is different from 2005 because of the switch from VSA to VSTA. 2008 does not have any of the problems or limitations listed below.
Script Assemblies
First a bit of background information on how the script task and script component work. I think the
BOL entry for the script task
does a good job of explaining the compilation behavior:
VSA scripts must be compiled before the package runs. The Script task includes the option to precompile script into binary code when the package is saved. When script is precompiled, the language engine is not loaded at run time and the package runs more quickly; however, compiled binary files consume significant disk space. If you do not use the precompile option, the script is compiled at run time, which slows package execution but consumes less disk space. If storing large packages is not a problem, you should precompile your VSA scripts. Also, only precompiled script can be run in a 64-bit environment.
Whether you’re using the
PreCompile
option or not, the script will eventually be compiled into a .NET assembly. Loading the assembly takes time, uses memory, and requires a separate
AppDomain
.
2005 RTM
In the initial 2005 release, SSIS did not cache these Script Assemblies. This meant that if you were executing a script task inside of a loop, a new AppDomain would be created for each iteration, and the assembly is reloaded. This wasn’t good for performance, and because we can’t unload/destroy an AppDomain until the process finishes, could result in memory issues.
2005 SP1
Script assembly caching was introduced in SP1 to address the performance and memory issues. This functionality made sure that the script assembly was only loaded once, and then reused on subsequent executions. However, each script was associated with a GUID… if you copy/paste the .DTSX file, the GUID for the script task or script component stays the same (even if you later go in and modify the script code). This led to unexpected behavior – if the script code was completely different, but came from a copy/pasted script task, you’d end up reusing one of the script assemblies for both tasks. The
related KB article
describes the problem in more detail.
2005 SP2
This behavior was fixed in SP2 by changing the GUID every time the script is modified. This meant that if the script code was the same, and the name of the script task or component matched, the script assembly would only be loaded once.
2005 SP3
There was one remaining side effect of script caching that hadn’t turned up yet. This
KB article
describes the problem you might run into, but doesn’t give much detail (other than that it’s related to caching).
The issue was that if the same script is executed in parallel (multiple execute package tasks running the same child package in process), they might share the same Dts runtime object (which has collections for variables, and connection managers). So if a script in the first instance sets variable Foo to “A”, and the second instance sets it to “B”, the first instance would end up with a value of “B” as well.
You can see why this would be a problem (and extremely confusing).
Unfortunately this problem isn’t easily fixed without entirely disabling script caching. Because we don’t want to go back to the RTM behavior (no caching at all), we introduced a new property to the Script Task / Component to control caching -
OptimizeScriptExecution
. If you’re running a child package with scripts multiple times in parallel, be sure to set this value to False, which disables the cache and prevents the scripts from sharing a runtime object.
I hope that helps!
