SSIS: Event Firing within Script Tasks

When using the Script component is SSIS, I have noticed that alot of people don't take advantage of the IDTSComponentEvents Interface in the DTS namespace. This interface allows for event firing. All the existing tasks use this interface to fire the events required during their execution. Standard events include OnError, OnWarning, OnInformation and OnProgress. The Script Task can use this interface to easily portray to the Progress Tab, SSIS Logging or the EventHandler exactly what is happening in the script. I include it in all my script tasks for error handling but it can be used alot more extensively depending on the complexity. For instances showing the progress as you loop through items in a collection.

The first thing to do when setting this up is to add the System variable TaskName to the ReadOnly variables collection list.

The Code will look as follows :

 

Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        Dim SubComponent As String = String.Empty

        Try
            ' Get local instances of variables collection
            Dim oVars As Variables = Dts.Variables

            ' Get Task name
            SubComponent = oVars.Item("System::TaskName").Value.ToString

            ' Loop abb 10 times
            For i As Integer = 1 To 10
                Dts.Events.FireProgress("My Message : Wow look at me go!", i * 10, 0, 0, SubComponent, True)
            Next

            ' Express loop success
            Dts.Events.FireInformation(0, SubComponent, "Looping Succeeded", String.Empty, 0, True)

            ' Show Error
            Dts.Events.FireError(0, SubComponent, "This would be my error!", String.Empty, 0)

            Dts.TaskResult = Dts.Results.Success
        Catch exDTS As DtsException
            Dts.Events.FireError(CInt(exDTS.ErrorCode), SubComponent, exDTS.Message.ToString, String.Empty, 0)
        Catch ex As Exception
            Dts.Events.FireError(0, SubComponent, ex.Message.ToString, String.Empty, 0)
        End Try

    End Sub

End Class

SSIS shows the progress as :


These events can also be captured by SSIS logging or the SSIS event handler.