Execution SSIS package in .NET:  ADO NET Source with Expressions Problem
Hello Gurus! We try to execute our SSIS packages from a .NET application. It works fine until we use “Expressions ” inside Data Flow’s components like “ADO NET Source” (e.g., for building SQL statements) I use the ADO NET Source Control and want to set the SqlCommand property with an expression holding a SQL statement. When executing the SSIS package in Visual Studio (in debug mode) I get no problem, everything works fine. When executing the package from within my .NET application it crashes the app completely, only creating a generic Win32 Exception with no relevant information. My steps: 1. I created new SSIS package. 2. Then added the Data Flow component. 3. Inside Data Flow I added “ADO NET Source”. I use this control in order to get data from Webtrends (a third party data provider). It uses an ODBC driver provided by Webtrends to access the data. SSIS allows me to access ODBC only via ADO NET provider, so that’s why I use ADO NET Source. The problem is not related to the Webtrends driver, we managed to reproduce the error with a connection to a SQL Server. 4. I would like to have only one SSIS package which can get all the data I need from Webtrends. I just want to set some variables of the SSIS package from within C# (which works fine). When I set variables from C#, I use expressions to generate new SQL statements for the ADO.NET source using the values from the given variables. Unfortunately that does not work. I get an error and the application crashes. 5. If I do not use Expressions, (by setting the SqlCommand directly in the package) then everything works fine and I can execute package from .Net, but even if I only want to set a hardcoded SQL statement within Expression I get an error…. If I understand correctly there is only one “DTS” framework which is used from SQL Server, Visual Studio and even from .Net/C#. So I guess that it should work also from .Net without any problem, but probably it messes some parameters which I should set in C# for the application object before package executing. If someone has already experienced this problem and has a solution please provide me with information on how to fix it. Our environment: VS 2008 SQL Server 2008 R2 References: Microsoft.SqlServer.DTSRuntimeWrap (version 10.0.0.0) Microsoft.SQLServer.ManagedDTS (version 10.0.0.0) Microsoft.SqlServer.DTSPipelineWrap (version 10.0.0.0) Here is my c# code: dtsApplication = new dts.Application (); using (dts.Package dtsPackage = dtsApplication.LoadPackage(packagePath, null )) { packageResult = dtsPackage.Execute(); } Here are some screenshots showing my packages: You can see it’s just so simple… Control Flow: http://img269.imageshack.us/img269/2882/controlflow.jpg Data Flow: http://img140.imageshack.us/img140/4105/dataflow.jpg ADO NET Source: http://img227.imageshack.us/img227/2667/adonetsource.jpg Thanks.
October 12th, 2010 4:11pm

Why you did you use Microsoft.SqlServer.DTSRuntimeWrap and not Microsoft.SqlServer.Dts.Runtime?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2010 5:52pm

Hi ArthurZ! thanks a lot for reply. i use exactly Microsoft.SqlServer.Dts.Runtime which is in Microsoft.SQLServer.ManagedDTS assembly. i also use Microsoft.SqlServer.DTSRuntimeWrap and Microsoft.SqlServer.DTSPipelineWrap for dynamic column mapping but it is nothing to do with the described problem. so my "using" in the class looks like: using dts = Microsoft.SqlServer.Dts.Runtime; best regards,
October 12th, 2010 8:07pm

Please provide with the error message.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2010 8:34pm

"An unhandled win32 exception occurred in <process name>." there is no clear error message, it just crashes and i get the message like shown on the picture: http://img149.imageshack.us/img149/9297/erroreu.jpg WBR
October 12th, 2010 9:33pm

Please let us know: If you are running this package in BIDS with the expression, does it succeed (do you run your BIDS as admin)? And Do you run the C# app on the same machine using the same credentials (as admin)?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2010 10:16pm

Hi! i simply tried to open package and execute - no error. executing from BIDS - no error. yes i am admin and i run BIDS, Visual Studio and my .net App on the same PC under Admin account. i think it is a problem with settings for DTS Application i am creating in .net, maybe i have to set correctly some properties, but i have no idea which of them. Thanks.
October 13th, 2010 12:47pm

We had the same problem. Setting properties like sql statements in a dataflow using container expressions causes some hosts to crash. Our problem was with VSTestHost, which crashed in this way. The end result was we had to switch to NUnit. MS closed our issue with a 'could not reproduce'. The only interesting thing is that VSTestHost did not work but NUnit did, meaning the problem can be changed by altering some aspect of the hosting process. I suspect it might have something to do with COM. The only thing that might help is that somewhere there is some config setting that changes how 'legacy' unhandled exceptions are dealt with, or something like that. I can't remember the setting, but there is a certain type of exception that can be ignored or not, depending on some setting. Tech Lead
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2010 11:16am

Would you believe it? We actually just solved this one. What you need is to change the STAThread attribute to MTAThread. Then it works. Tech Lead
October 15th, 2010 12:41pm

Would you believe it? We actually just solved this one. What you need is to change the STAThread attribute to MTAThread. Then it works. Tech Lead Yes, it also worked for me! Thank You very very much!!! The problem is solved.
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2010 8:01am

I have exactly the same scenario. Runtime expressions inside data flow containers. However it still fails with a vsTestHost error? Did you apply the [MTAThread] attribute to the C# method calling the package? I am running it using mstest and applied the attribute to the unit test method. However it still falls over.
February 22nd, 2011 10:50am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics