Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Get Progress Information for SSIS Script Tasks


By:   |   Read Comments   |   Related Tips: 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:

Parameter

Data Type

Description

informationCode

Int32

A number that identifies the error condition. If you are in the context of a try block you can pass the exception's error code  or any number you want.

subComponent

String

Usually you send the task's name.

description

String

This is where you describe the error.

helpFile

String

The path of a help file, but you can send a null string.

helpContext

Int32

This value identifies the topic of the help file specified on the previous parameter. You can set this value to zero.

fireAgain

Boolean

According to Microsoft it indicates this should continue firing or stop firing. However, I have never used this feature.

SSIS FireError Event

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

Parameter

Data Type

Description

errorCode

Int32

A number that identifies the error condition. If you are in the context of a try block you can pass the exception's error code or any number you want.

subComponent

String

The task name.

description

String

This is where you describe the error.

helpFile

String

The path of a help file, but you can send a null string.

helpContext

Int32

This value identifies the topic of the help file specified on the previous parameter. You can set this value to zero.

SSIS FireProgress Event

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

Parameter

Data Type

Description

progressDescription

Int32

A number that identifies the error condition. If you are in the context of a try block you can pass the exception's error code or any number you want.

percentComplete

Int32

Indicates how much of the task is completed.

progressCountLow

Int32

The low order DWORD of a UInt64 value (also a QWORD) that indicates the units of work completed. Don't be afraid, you can set this to zero.

progressCountHigh

Int32

The High order DWORD of a UInt64 value (also a QWORD) that indicates the units of work completed. Don't be afraid, you can set this to zero.

subComponent

String

The task name.

fireAgain

String

According to Microsoft it indicates this should continue firing or stop firing.  However, I have never used this feature.

SSIS FireWarning Event

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

Parameter

Data Type

Description

warningCode

Int32

A number that identifies the warning condition, but you can use any number you want.

subComponent

String

The task name.

description

String

A description for the warning.

helpFile

String

The path of a help file, but you can send a null string.

helpContext

Int32

This value identifies the topic of the help file specified on the previous parameter. You can set this value to zero.

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.

Sample SSIS Package Layout.

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.

Script Task Settings.

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.

FireProgress Task Execution.

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.

FireWarning Task Execution.

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.

Setting Up Package and Task Error Configuration.

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

Package Execution.

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

Package Execution Log.
Next Steps


Last Update:


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools