Catching events from an SSIS package loaded in C#

Question

I have an SSIS package that executes several tasks. I manually added an event handler inside Business Intelligence Studio 2005 at the package level for the OnExecStatusChanged event.

My question is, how can I add a handler for this event inside C#? I have loaded the package as pointed here and I also created a custom class inherited from Microsoft.SqlServer.Dts.Runtime.DefaultEvents which is my "Listener":

    Microsoft.SqlServer.Dts.Runtime.SqlPackageEventListener sqlListener = new SqlPackageEventListener();
    Microsoft.SqlServer.Dts.Runtime.Application sqlPackageLoader = new Application();
    Microsoft.SqlServer.Dts.Runtime.Package sqlPackage = sqlPackageLoader.LoadPackage(@"path\MigrateData.dtsx", sqlListener);
    sqlPackage.Execute(null, null, sqlListener, null, null);

If I check the sqlPackage.EventHandlers.Count property I get the right number for handlers added inside Business Intelligence Studio.

Is there some way to handle those events inside C#?

Thanks.

Answer 1

Well I did not find anything so I came up with a work around so I will auto-respond to me:

Since there is no way to directly catch the events that the SSIS package make then I implemented my own events inside my listener:

public class SqlPackageEventListener : DefaultEvents
{
    public SqlPackageChangedHandler OnPackageError;

    public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError) {
     OnPackageError(this, new PackageErrorEventArgs(source, subComponent, description));
     return base.OnError(source, errorCode, subComponent, description, helpFile, helpContext, idofInterfaceWithError);
    }

    public delegate void SqlPackageChangedHandler(
     object sqlPackage,
     EventArgs packageInfo
     );
}

public class PackageErrorEventArgs : EventArgs 
{
    private DtsObject source;
    public DtsObject Source {
     get { return source; }
     set { source = value; }
    }

    private string subcomponent;
    public string Subcomponent {
     get { return subcomponent; }
     set { subcomponent = value; }
    }

    private string description;
    public string Description {
     get { return description; }
     set { description = value; }
    }

    public PackageErrorEventArgs(DtsObject source, string subcomponent, string description) {
     this.description = description;
     this.source = source;
     this.subcomponent = subcomponent;
    }
}

public class Test 
{
    SqlPackageEventListener sqlListener = new SqlPackageEventListener();
    sqlListener.OnPackageError += new SqlPackageEventListener.SqlPackageChangedHandler(sqlListener_OnPackageError);
    Microsoft.SqlServer.Dts.Runtime.Application sqlPackageLoader = new Microsoft.SqlServer.Dts.Runtime.Application();
    Microsoft.SqlServer.Dts.Runtime.Package sqlPackage = Microsoft.SqlServer.Dts.Runtime.sqlPackageLoader.LoadPackage(@"path_to\file.dtsx", sqlListener);
    sqlPackage.Execute(null, null, sqlListener, null, null)

    public void sqlListener_OnPackageError(object sender, EventArgs args) {
     //code to handle the event
    }
}

So the "trick" is to add a delegate to your "Listener" object that you pass to the LoadPackage method of the Package object, that way, we can access the "OnError" override inside the listener and raise the event. As you can see I implemented my own EventArguments class so we can pass important data to our handler code and see what package is running or any other information that you can get from overriding the methods when you inherit from DefaultEvents.

Of course I only implemented OnError here, you can implement any other handler you like that is supported by SQL Server and that can be overrided since that is the scope where we raise the event.

That way I can create my SqlPackageEventListener object and handle it's "OnPackageError" event with the sqlListener_OnPackageError method and do whatever I need in case of any error that the execution of the SSIS package caused.