Configuring .NET for running SSIS packages from custom applications

  • Article

If you execute SSIS packages from custom applications, you own the application and thus you are responsible for configuring .NET runtime properly to get the maximum performance.

.NET configuration is usually performed using .exe.config files, so it is a just matter of providing good config file. How do you know what is good? The simplest way is to look at the config file that SSIS provides, and copy the appropriate settings. Now let's take a look at DTExec.exe.config provided with SQL Server 2008 and discuss the choices made by SSIS team.

<configuration>
<startup>
<requiredRuntime version="v2.0.50727"/>
</startup>
<runtime>
<gcServer enabled="true"/>
<disableCommitThreadStack enabled="true"/>
<generatePublisherEvidence enabled="false"/>
</runtime>
</configuration>

requiredRuntime - this line simply tells which version of .NET you want to use. 2.0 or above should be good.

gcServer - DTEXEC uses the "server" version of garbage collector. Server GC performs better for typical SSIS load, especially on multiprocess machines. This is important if your SSIS package uses managed transforms (e.g. ADO.NET source, script transform, or custom transform written in .NET).

disableCommitThreadStack - by default .NET commits the thread stacks (i.e. reserves memory for stacks - usually from page file). But SSIS creates a lot of threads, while typically uses little stack space, so it performs better if stack memory is not immediately committed when the thread is created. With this option your application might perform a bit better and require smaller page file (note that anyway, the page file is not really used until needed). The drawback of this choice is that application might fail if Windows is totally out of memory, and application can't extend its stack. But in this situation something has already gone bad, and it is probably better to fail fast in this case anyway.

generatePublisherEvidence - this tells .NET runtime the DTEXEC does not use Publisher evidence, and thus .NET does not have to verify authenticode signatures. This increases the startup performance a little bit, but mainly prevents problems that may occur when authenticode checks certificate revocation list.

Now that you know these options made by SSIS team, you may test and decide whether they are appropriate for your application as well, and copy them to your application's config file if needed.

P.S. Also make sure you create SSIS package using MTA thread, see Matt's blog for details:
https://blogs.msdn.com/mattm/archive/2007/09/14/running-packages-from-custom-applications.aspx