Get Progress Information for SSIS Script Tasks

By: Daniel Farina   |   Comments   |   Related: More > Integration Services Development

Problem

You have a SSIS package that includes one or more Script Tasks that take a considerable amount of time to finish. In this tip I will show you how to get progress information so you can monitor the SQL Server Integration Services Package execution for Script Tasks.

Solution

A common DBA task is to monitor SSIS package execution.  Some of the things we need to know are: how much longer before a process ends, why did it fail, can we re-run the package, etc. Sometimes there are easy answers, if a Data Flow Task is running you look at the log output for the processed rows count and compare it with the number of rows in the data source, but what about when the task that is running is a Script Task. Even worse is when you are not the author of the SSIS package. That's the point when we are out of answers.

The truth is that not many developers know about the capability of SSIS packages to raise events which can be useful to track package progress.  We will look at several ways this can be done in SSIS.

Raising SSIS Events

Events give us the possibility to report errors, warnings, status messages and of course, send progress information about the task to the package. The DTS object in the Script Tasks has a property called "Events" that contains methods that allow us to perform such operations.

This list enumerates these methods:

• FireCustomEvent: Used to raise events defined by the user.
• FireError: Used to send error conditions about the task to the package.
• FireInformation: Used to send informational messages.
• FireProgress: This event is to send progress information about the task.
• FireQueryCancel: According to Microsoft, returns a value that indicates whether the package needs the task to shut down prematurely.
• FireWarning: This event is used to send an alert to the client.

Now I will go through the most common SSIS Events.

SSIS FireInformation Event

These are the parameters and an explanation for the SSIS FireInformation Event:

SSIS FireError Event

These are the parameters and an explanation for the SSIS FireError Event:

SSIS FireProgress Event

These are the parameters and an explanation for the SSIS FireProgress Event:

SSIS FireWarning Event

These are the parameters and an explanation for the SSIS FireWarning Event:

Example to Track SSIS Package Execution Status with Script Tasks

In order to put it all together lets create a simple SSIS project with three Script Tasks like in the next image.

On each Script Task select Microsoft Visual Basic as the script language and add a Read Only variable "System::TaskName". This will serve to identify where the events come from.

How to Send Progress Information in an SSIS Script Task

Suppose we need to read data from a source and then perform some task on it. In the first script task we execute a query to sys.objects in the context of the master database to simulate the input data we need to process.

If we want to send progress information we must establish what would be a 100 percent of the task. In our case that is the number of rows returned by our previous query to sys.objects. Also we need to define a factor of completion, a number between 1 and 99 that we will use as a fraction of the task. I selected a factor of 10 to report the progress. The next step is to divide the 100 percent by the factor we defined to get how many rows are needed to be processed to get one factor unit, in this case the 10 percent.

With that done, the only thing remaining is adding a counter to keep track of the number of processed rows. Our task will reach a 10 percent progress every time the number of processed rows is divisible by the factor of completion, in other words, when the modulo of that division is zero.

Below is the code I used for this Script Task. If you take a look at it you can see that I also raised an information event with the message "This is a sample for MSSQLTips.com" prior to processing rows.

Public Sub Main()

Dim oCnn As New OleDb.OleDbConnection("Data Source=ORIOM;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;")
Dim oCommand As OleDb.OleDbCommand
Dim oReader As OleDb.OleDbDataReader
Dim oDT As New DataTable
Dim oRow As DataRow
Dim iRowCount As Integer
Dim iProcessedRows As Integer = 0
Dim iStep As Integer

Dts.Events.FireInformation(5, Dts.Variables("System::TaskName").Value.ToString,
      "This is a sample for MSSQLTips.com", vbNullString, 0, False)
Try
    oCnn.Open()
    oCommand = oCnn.CreateCommand()
    oCommand.CommandText = "SELECT * FROM sys.objects;"
    oCommand.CommandType = CommandType.Text

    oReader = oCommand.ExecuteReader()
    oDT.Load(oReader)

    'We store the rows to process. That defines the 100% of the task's progress
    iRowCount = oDT.Rows.Count

    iStep = Convert.ToInt32(Abs(iRowCount / 10))
    For Each oRow In oDT.Rows
 '
 ' Here we would do our processing.
 '

 ' Check if we reach a 10 percent of work done.
 If Convert.ToInt32(Abs(iProcessedRows Mod iStep)) = 0 Then
     Dts.Events.FireProgress("Reading Master Objects:",
        Convert.ToInt32(Abs(iProcessedRows / iStep)) * 10,
        0, 0, Dts.Variables("System::TaskName").Value.ToString, False)
 End If
 iProcessedRows = iProcessedRows + 1
    Next

    Dts.TaskResult = ScriptResults.Success
Catch ex As DtsException
    Dts.Events.FireError(Convert.ToInt32(ex.ErrorCode), "", "", "", 0)
    Dts.TaskResult = ScriptResults.Failure
End Try

End Sub

Next is a screen shot showing the execution of this task. Notice that the messages are preceded by the name of the task and also the type of the event, in this case Information and Progress.

What About Warning and Error Messages in SSIS Script Tasks

Firing warning and error messages is much of the same process, but without all the added logic to compute the task's progress. Take a look at the next code where I fired two warnings, one with the Task's name and the other with an arbitrary string value.

Public Sub Main()
 Dts.Events.FireWarning(1, Dts.Variables("System::TaskName").Value.ToString(), "This is a Sample Warning.", vbNullString, 0)


 Dts.Events.FireWarning(1, "blah", "This is a Warning without a valid subComponent.", vbNullString, 0)

 Dts.TaskResult = ScriptResults.Success
End Sub

On the next image is the output of this task's execution in which you can see that the warning we fired with an arbitrary string as the task's name has that string enclosed with brackets as if that were the task's name.

Error Messages and Task Failure in SSIS Script Tasks

Something I must add about this matter is that firing error messages doesn't mean that the task will stop execution and fail. The reason behind this is that we are firing event messages not exceptions. An exception is an unrecoverable error that makes the package abort execution. When we fire an error event we are informing the SSIS Execution Engine that an error happened and the Engine decides according to the Maximum Error Count value if it has to abort package execution or not.

Take a look at the code below; there are two calls to FireError event.

Public Sub Main()

 Dts.Events.FireError(1, Dts.Variables("System::TaskName").Value.ToString(), "Sample Error Message", vbNullString, 0)

 Dts.Events.FireError(1, Dts.Variables("System::TaskName").Value.ToString(), "Oops! Another Error", vbNullString, 0)

 Dts.TaskResult = ScriptResults.Success
End Sub

What we are going to do is to configure the property MaximumErrorCount on both the Script Task and the package with a value of 3 like on the next image.

This way even when two error events are raised, the SSIS package completes successfully.

The next screen capture is the output of the execution log.

Next Steps

• You can download this sample SSIS package form here.
• If you want to enrich your SSIS packages and make them look more professional, take a look at Integration Services Best Practices Tips Category.
• Also check out SQL Server Business Intelligence Tips and Tricks.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips