Hi,
Ive been taking a look at how to consume events from a package when executing programatically.
Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.
My class that implements IDtsEvents simply output a message to the console for each event type.
Weird thing is, when I execute, this is the only output I get:
Starting...
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Package
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Sequence
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.TaskHost
OnPostValidate:Microsoft.SqlServer.Dts.Runtime.TaskHost
OnQueryCancel
Package ran successfully
What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.
Anyone know why i dont see all of the events?
Thanks
Jamie
Heres the code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
namespace Package_API
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting...");
Package p = new Package();
p.InteractiveMode = true;
p.OfflineMode = true;
// Add a Script Task to the package.
Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence");
TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask");
// Run the package.
DtsEvents events = new DtsEvents();
p.Execute(null,null,events,null,null);
//p.Execute();
if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend)
Console.WriteLine("Package failed or abended");
else
Console.WriteLine("Package ran successfully");
Console.ReadLine();
}
}
}
// Class that implements the IDTSEvents interface:
public sealed class DtsEvents : IDTSEvents
{
void IDTSEvents.OnPreExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreExecute: " + exec.ToString());
}
void IDTSEvents.OnBreakpointHit(IDTSBreakpointSite breakpointSite, BreakpointTarget breakpointTarget)
{
Console.WriteLine("OnBreakpointHit");
}
void IDTSEvents.OnCustomEvent(TaskHost taskHost,string eventName,string eventText,ref Object[] arguments,string subComponent,ref bool fireAgain)
{
Console.WriteLine("CustomEvent");
}
void IDTSEvents.OnPreValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreValidate: " + exec.ToString());
}
void IDTSEvents.OnPostValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostValidate:" + exec.ToString());
}
void IDTSEvents.OnWarning(DtsObject source,int warningCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnWarning");
}
void IDTSEvents.OnInformation(DtsObject source,int informationCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError,ref bool fireAgain)
{
Console.WriteLine("OnInformation");
}
void IDTSEvents.OnPostExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostExecute");
}
bool IDTSEvents.OnError(DtsObject source,int errorCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnError");
return true;
}
void IDTSEvents.OnTaskFailed(TaskHost taskHost)
{
Console.WriteLine("OnTaskFailed");
}
void IDTSEvents.OnProgress(TaskHost taskHost,string progressDescription,int percentComplete,int progressCountLow,int progressCountHigh,string subComponent,ref bool fireAgain)
{
Console.WriteLine("OnProgress");
}
bool IDTSEvents.OnQueryCancel()
{
Console.WriteLine("OnQueryCancel");
return true;
}
void IDTSEvents.OnExecutionStatusChanged(Executable exec,DTSExecStatus newStatus,ref bool fireAgain)
{
Console.WriteLine("OnExecutionStatusChanged");
}
void IDTSEvents.OnVariableValueChanged(DtsContainer DtsContainer,Variable variable,ref bool fireAgain)
{
Console.WriteLine("OnVariableValueChanged");
}
}
By returning true from OnQueryCancel, you are cancelling the package 
Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled.
Return false from this method, or inherit from DefaultEvents and only override methods that you actually need.
|||
 Michael Entin - MSFT wrote: |
| |
| By returning true from OnQueryCancel, you are cancelling the package Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled. Return false from this method, or inherit from DefaultEvents and only override methods that you actually need. |
DOH!!!
What a dumbass. I should have realised that!
Thanks Michael!
-Jamie
|||
How odd, Jamie. According my class I can follow each event -including post and pre executing...
Dim EventsSSIS As EventosSSIS
EventsSSIS = New EventosSSIS()
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)
Public Class EventosSSIS
Implements IDTSEvents
Public proceso As Int16 = 0
Sub OnPostValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostValidate
End Sub
Sub OnProgress(ByVal taskHost As TaskHost, ByVal progressDescription As String, ByVal percentComplete As Integer, ByVal progressCountLow As Integer, ByVal progressCountHigh As Integer, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnProgress
End Sub
Sub OnPreExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreExecute
End Sub
Sub OnPreValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreValidate
End Sub
Sub OnPostExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostExecute
End Sub
Sub OnWarning(ByVal source As DtsObject, ByVal warningCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) Implements IDTSEvents.OnWarning
End Sub
Sub OnInformation(ByVal [source] As DtsObject, ByVal informationCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnInformation
End Sub
Sub OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed
End Sub
Function OnError(ByVal source As DtsObject, ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) As Boolean Implements IDTSEvents.OnError
End Function
Sub OnExecutionStatusChanged(ByVal exec As Executable, ByVal newStatus As DTSExecStatus, ByRef fireAgain As Boolean) Implements IDTSEvents.OnExecutionStatusChanged
End Sub
Sub OnCustomEvent(ByVal taskHost As TaskHost, ByVal eventName As String, ByVal eventText As String, ByRef arguments() As Object, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnCustomEvent
End Sub
Sub OnBreakpointHit(ByVal breakpointSite As IDTSBreakpointSite, ByVal breakpointTarget As BreakpointTarget) Implements IDTSEvents.OnBreakpointHit
End Sub
Sub OnVariableValueChanged(ByVal dtsContainer As DtsContainer, ByVal variable As Variable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnVariableValueChanged
End Sub
Public Overloads Function OnQueryCancel() As Boolean Implements IDTSEvents.OnQueryCancel
Dim cancelar As Int32 = 0
OnQueryCancel = False
Try
Using cn As New SqlConnection(sCadenadeConexion)
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
cm.CommandType = Data.CommandType.Text
cm.CommandText = "SELECT cancelar FROM sis_controlthread where idproceso= " & proceso
cancelar = cm.ExecuteScalar
If cancelar Then
OnQueryCancel = True
Else
OnQueryCancel = False
End If
cm.Dispose()
End Using
cn.Close()
End Using
Catch ex As Exception
TratamientoErrores(0, 0, 11, ex.Message, "On Query Cancel")
End Try
End Function
End Class
