Freigeben über

You may see weird behavior when using the SSIS Object Model from an ASP.NET application

  • Artikel

SSIS comes along with a rich set of application programming interfaces (APIs) for programming the Integration Services object model. You can extend packages by writing components that become available within SSIS Designer to provide custom functionality in a package. You can create, configure, and run packages programmatically from your own applications. The SSIS object model can be used either from a windows or a web based application. However using it from an ASP.NET application may not always give you the most expected results. Some of the pros and cons are discussed in this blog.

Recently I was working with a customer who was trying execute a SSIS package programmatically from an ASP.NET application. The package used the SendMail task to send emails to user. The task had three properties which were being evaluated using an expression from local SSIS variables. The values of the local variables were dynamically being passed from the ASP.NET application. Everything worked fine but sometimes the package failed from the ASP.NET application. As opposed to this, the package ran successfully every time from BIDS or from Management Studio. If I use the same code to execute the package from a console app, it ran successfully every time. So there was something different from the ASP.NET application. To understand it better, I set up a repro in my lab machine and also did some instrumentation to my code. The variable values were being updated properly but still the package failed. On Error event handler captured the following error:

 Microsoft.SqlServer.Dts.Runtime.TaskHostThe result of the expression 
"@[User::MailRecipient]" on property "ToLine" cannot be written to the property. 
The expression was evaluated, but cannot be set on the property.

I also observed that I cannot access the Properties.GetValue or Properties.SetValue of the TaskHost object as described in here.

If you try this from a console app, it works fine, but breaks every time from an ASP.NET application. Getting this exception:

Attempted to access an unloaded AppDomain.

[AppDomainUnloadedException: Attempted to access an unloaded AppDomain.]

Microsoft.SqlServer.Dts.Runtime.DtsProperty.SetValue(Object o, Object value) +146

SSISWebApp._Default.Page_Load(Object sender, EventArgs e) in D:\SSISWebApp\SSISWebApp\Default.aspx.vb:73

System.Web.UI.Control.OnLoad(EventArgs e) +91

System.Web.UI.Control.LoadRecursive() +74

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Surprisingly every time I made any changes in my ASP.NET code and rebuild my assembly, I landed into these errors until I recycle my app pool. Once I recycle the app pool the code executes without any error. We did some live debugging and found that it’s a bug in the SSIS object model.

What we discovered is that within the w3wp process, our DTS assemblies are stored within the Shared AppDomain whereas the actual ASP.NET application is housed in a separate AppDomain for itself. When any change is made to the assembly in the project AppDomain, it is completely torn down due to ASP.NET AppDomain’s inability to dynamically load DLLs. When this occurs, any subsequent attempts to access the properties on a TaskHost fails due to some vestigial reference in our code in the shared AppDomain back to the original. Recycling the AppPool resolves the issue, but any subsequent updates to the application will require another recycle. The issue occurs due to caching that our DLLs do in order to avoid doing reflection multiple times on COM components. Cross AppDomain interactions have long been established as a weak point of SSIS due to this limitation.

Also, the issue is not limited to our SendMail Task. It would occur on any Tasks/Components since they all boil down to underlying COM objects. Users would face this behavior more on their test environments as compared to production. Because while testing we intend to change our code multiple times and rebuild our app but we seldom do that in production. If you are facing the above issue I would recommend two simple workarounds:

  1. Isolate your application in a separate application pool.
  2. Recycle your app pool every time you rebuild your assembly. Since the app is running on a separate app pool, it wont affect other applications.