Hi

I'm trying to call a package using C# .I used a wrapper around its objects
model and now successfully load all the DTS packages in the database server
I want.However there is a big problem ,when I try to execute my package the
OnQueryCancel method is called unexpectedly and package dosen;t get
executed.There is a KB for that at
http://support.microsoft.com/kb/321525/EN-US/
but it is exactly what I am doing Look at my code:

using System;
using Microsoft.SQLServer.DTSPkg80;
using System.Runtime.InteropServices;

namespace Company.Business.ClassFiles.DTS
{

public class DTSIntrop
{

private string databaseServer;

public DTSIntrop(string databaseServer)
{
this.databaseServer=databaseServer;

}

public PackageInfos GetPackageNames(bool returnLastVersion)
{
ApplicationClass application = new ApplicationClass();
PackageSQLServer pkgSQLServer =
application.GetPackageSQLServer(this.databaseServe r,"","",DTSSQLServerStorageFlags.DTSSQLStgFlag_Use TrustedConnection);
PackageInfos pkgInfos =
pkgSQLServer.EnumPackageInfos("",returnLastVersion , "");

return pkgInfos;
}

public void ExecutePackage(string packageName)
{

try
{
Package2Class package = new Package2Class();
UCOMIConnectionPointContainer CnnctPtCont =
(UCOMIConnectionPointContainer) package;
UCOMIConnectionPoint 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);
CnnctPt.Advise(null, out iCookie);
object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(this.databaseServer, null, null,
DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedC onnection, null,
null, null, packageName, ref pVarPersistStgOfHost);

package.Execute();
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie); //a connection that is created by
IConnectionPoint.Advise must be closed by calling IConnectionPoint.Unadvise
to avoid a memory leak
}

catch(System.Runtime.InteropServices.COMException ex)
{
Exception nEx=new Exception(String.Format("COMException {0}\n{1}\n{2}",
ex.ErrorCode, ex.Message, ex.StackTrace),ex);
throw nEx;
}

catch(System.Exception ex)
{
Exception nEx=new Exception(String.Format("Exception\n{0}\n{1}",
ex.Message, ex.StackTrace),ex);
throw nEx;
}

}
}


internal class PackageEventsSink :
Microsoft.SQLServer.DTSPkg80.PackageEvents
{
static string packageStatus="Not initialized";

public void OnQueryCancel(string EventSource, ref bool pbCancel)
<---***Every time I try to execute a package this method is called twice and
package dosen't get executed****
{
packageStatus = string.Format("OnQueryCancel({0})", EventSource );
pbCancel = false;
}
public void OnStart(string EventSource)
{
packageStatus = string.Format("OnStart({0})" ,EventSource);
}
public void OnProgress(string EventSource, string ProgressDescription, int
PercentComplete, int ProgressCountLow, int ProgressCountHigh)
{
packageStatus = string.Format("OnProgress({0})",EventSource);
}
public void OnError(string EventSource, int ErrorCode, string Source,
string Description, string HelpFile, int HelpContext, string
IDofInterfaceWithError, ref bool pbCancel)
{
packageStatus = string.Format("OnError({0})",EventSource);
}
public void OnFinish(string EventSource)
{
packageStatus = string.Format("OnFinish({0})",EventSource);
}

} // class PackageEventsSink : DTS.PackageEvents

}

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote