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.
I created new SSIS package.
Then added the Data Flow component.
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.
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.
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.
SQL Server 2008 R2
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 ();
(dts.Package dtsPackage = dtsApplication.LoadPackage(packagePath,
packageResult = dtsPackage.Execute();
Here are some screenshots showing my packages:
You can see it’s just so simple…
ADO NET Source: