Hello,

I'm writing a windows service in .NET 2.0 (C#) that executes and logs
the results of various types of ETL jobs (SQL Server 2000 DTS & Data
Junction currently).

This works fine on individual packages, but when I execute a package
that executes other packages via the Execute Package Task then only the
Package Start, Finish and Error events fire. I thought that perhaps
this was by design, but when I execute this package through Enterprise
Manager, or as a subprocess in Data Junction, the detailed progress
from the nested packages is shown.

My sample packages are very simple. The parent package has a single
execute package task with default settings. The child package has a
transform data task between a SQL Server 2000 data source and a text
file, again with default settings.) The tasks are all native SQL
Server 2000 DTS tasks, no VB6 custom tasks that might have threading
issues.

If I set the Execute Package Task in the parent package and the Data
Pump task in the child package to "Execute on the Main Thread" then I
can execute them in C# as a subprocess in a Data Junction package
successfully with progress information. However if I try to execute
the same package using the code below, the package fails with a
description of "Exception of type 'System.OutOfMemoryException' was
thrown.". Just for grins, I reduced the number of rows the transform
processed to 2000, shrinking the resulting data file to 140k, I still
received the out of memory error.

I've seen KB article 319048 "PRB: Cannot Use Data Transformation
Services Event Handlers in Visual Basic with Execute Package Task".
The article applies to packages executed in VB6, and unlike in the
article my code isn't crashing, but I thought that it might be a COM
interop threading issue, so I tried executing the package on both MTA
and STA threads with the same result.

Here is my test code (which is just the MSDN sample code, slapped into
a console app project and upgraded to use the new .NET
IConnectionPointContainer and IConnectionPoint interfaces):

[STAThread]
static void Main(string[] args)
{

Package2Class package2 = new Package2Class();


IConnectionPointContainer CnnctPtCont =

(IConnectionPointContainer)package2;

IConnectionPoint CnnctPt;

PackageEventsSink PES = new PackageEventsSink();

Guid guid = new
Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"); // UUID of PackageEvents
Interface

CnnctPtCont.FindConnectionPoint(ref guid, out
CnnctPt);

int iCookie;

CnnctPt.Advise(PES, out iCookie);

object pVarPersistStgOfHost = null;

//Retrieve global settings from the Configuration
File
package2.LoadFromSQLServer("CHEROKEE",
null,
null,

DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedC onnection,
null,
null,
null,
"Hootie3",
ref pVarPersistStgOfHost);


package2.Execute();

package2.UnInitialize();
}


public class PackageEventsSink : PackageEvents
{

#region PackageEvents Members

void PackageEvents.OnError(string EventSource, int ErrorCode,
string Source, string Description, string HelpFile, int HelpContext,
string IDofInterfaceWithError, ref bool pbCancel)
{
Console.WriteLine("On Error");
pbCancel = true;
}

void PackageEvents.OnFinish(string EventSource)
{
Console.WriteLine("On Finish");
}

void PackageEvents.OnProgress(string EventSource, string
ProgressDescription, int PercentComplete, int ProgressCountLow, int
ProgressCountHigh)
{
Console.WriteLine("On Progress");
}

void PackageEvents.OnQueryCancel(string EventSource, ref bool
pbCancel)
{
//
}

void PackageEvents.OnStart(string EventSource)
{
Console.WriteLine("On Start");
}

#endregion
}


Any ideas? One other detail is that when I attempt to execute a DTS
package as a subjob of a Data Junction project, I get the exact error
mentioned in the KB article referenced above ("Need to run the object
to perform this operation" "Code execution exception:
EXCEPTION_ACCESS_VIOLATION").