Helpful information and examples on how to use SQL Server Integration Services.
There are a lot of different ways to run SSIS packages, and many situations call for a custom package launcher application. If you're coding your own launcher, one thing to keep in mind is that certain SSIS functionality (such as the Script task / component) requires a multithreaded apartment (MTA), and can fail or behave incorrectly when run in a single threaded apartment (STA). By default, VB.NET console applications run in STA.
I worked on a customer issue where their custom package launcher started failing on parent/child packages after they changed their loading mechanism. The parent package was failing with an obscure E_NOTIMPLEMENTED error code when trying to retrieve a child package from SQL Server. We also noticed the issue only occurred when both packages were loaded from SQL using a username and password - loading either one from file, or using windows integrated authentication made the problem disappear.
The issue turned out to be related to the threading apartment. When loading packages from SQL using a username/password, the Execute Package Task attempts to reuse the parent package's connection managers. When this is done, the runtime also tries to reuse any open recordset objects as an optimization. The underlying OLEDB code we use to retrieve the package requires the thread apartment to be MTA, and was returning the obscure error code when it failed.
Once we modified the app to run in MTA, everything starting working again! Doing this for a VB.NET app is really simple - just prefix your Main() function with MTAThread.
1: <MTAThread()> _
2: Sub Main()
5: End Sub
.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.
We had a similar problem: when dataflow components had their properties set by a container expression, this crashed the STAThread process. Took us an eternity to figure out what was wrong (switch to MTAThread)