Monday, January 8, 2007

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.

4 comments:

Anonymous said...

You write very well.

Anonymous said...

You explained that very clearly, especially the screenshot at the end. Thanks for the time-saver!

Rajeev Ranjan said...

Your implementation of Dts.Events.FireInformation has got me confused with its last parameter usage. MSDN (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.idtsinfoevents.fireinformation.aspx) says:
fireAgain
A Boolean that indicates whether the task should continue executing. A value of true indicates that the error should cause the task to cancel. A value of false indicates that the error should not stop the task from executing, but the task should continue.

You have passed the value "True" but still your screen shot shows that the FireError event was executed. Should it be True or False?

Rajeev Ranjan said...

Interestingly, I tried with your sample code and it works the same way with either values. Or does it behave differently under different circumstances?
Thanks.