Script Task and Event Logging in SSIS

SQL Server 2008 Integration Services offers extensive logging capabilities that allow you to record a variety of predefined events raised by packages and their components in a number of different formats and destinations (for more information on this subject, refer to Using SSIS Logging to Resolve Runtime Errors and Performance Issues). While the same configuration settings can be leveraged in the case of the Script Task, you have the option of triggering and tracking events in a considerably more customizable manner. The purpose of this presentation is to explore features that provide this flexibility.

In order to fully understand functionality that will be discussed throughout this article, we need to first review some of the principles that govern the built-in logging mechanism. As you might already know, SSIS relies on several providers to handle interaction with external stores where its events can be recorded. The providers are grouped into five categories, depending on the type of data format they generate (namely SSIS log provider for Text files, SSIS log provider for XML files, SSIS log provider for SQL Server, SSIS log provider for SQL Server Profiler, and SSIS log provider for Windows Event Log). Despite obvious differences in the output each of them yields, their configuration from the SSIS perspective is practically identical, so for the sake of brevity, we will focus only on the last of them, which dumps arbitrarily chosen event types in the Windows Application log.

Before we discuss its settings in more detail, let's create a sample package that will help us illustrate the underlying concepts throughout the course of our presentation. To accomplish this, launch Business Intelligence Development Studio and create a new project based on the Integration Services template. Next, drag the Script Task icon from the Toolbox and drop it on the Designer interface. Display its Editor dialog box, designate Visual Basic .NET 2008 as the ScriptLanguage, and confirm your choice by clicking on the OK command button.

Now, with our basic package structure in place, we are ready to review its logging options. As we mentioned earlier, we can leverage the default mechanism available to all package components. To access its settings, right-click on the empty area of the Designer interface of Business Intelligence Development Studio and select the Logging... entry from its context sensitive menu (alternatively, you can choose the same item from the SSIS top-level menu). In the resulting Configure SSIS Logs dialog box, you will find two checkboxes representing the package and its Script Task. In order to designate our intended provider, highlight the first of them (but ensure that it remains unchecked) and add the SSIS log provider for Windows Event Log to the list of logs to use for the container on the right hand side of the dialog box. Next, enable both the checkbox to the left of the Script Task entry, such that its background is clear (rather than grayed out), indicating that you will configure unique logging (rather than relying on those assigned to its parent), as well as the checkbox to the left of the SSIS log provider for Windows Event log entry on the Providers and Logs tab. Switch to the Details tab and take a note of different events that can be logged for the Script Task container (if you click on the Advanced command button, you will also have an option to designate specific pieces of data you want to include in the log).

As expected, by selecting (in the Configure SSIS Logs dialog box) individual types of events associated with the state of Script Task execution, you will be able to track their occurrences (by leveraging the same mechanism that applies to other SSIS components). However, this particular task also offers the ability to trigger events and generate corresponding log entries at arbitrarily chosen points of its code. This is accomplished using one of two mechanisms:

• invoking any of the methods (listed below) of IDTSComponentEvents interface of the Dts.Runtime namespace (documented in the MSDN Library), which provides the ability to fire events of a particular type and consequently (as long as the SSIS options described above are configured) are logged by a designated provider:
• Dts.Events.FireBreakpointHit - raises an event indicating that a breakpoint (inserted into the Script Task code) has been encountered.
• Dts.Events.FireCustomEvents - raises a custom event (relying on having such event defined within the Script Task code).
• Dts.Events.FireError - raises an error event (to turn on its logging, enable OnError setting on the Details tab of Configure SSIS Logs).
• Dts.Events.FireInformation - raises an informational event (to turn on its logging, enable OnInformation setting on the Details tab of Configure SSIS Logs).
• Dts.Events.FireProgress - raises an event indicating quantifiable progress in the task's execution (to turn on its logging, enable OnProgress setting on the Details tab of Configure SSIS Logs).
• Dts.Events.FireQueryCancel - raises an event determining whether task execution should be terminated (to turn on its logging, enable OnQueryCancel setting on the Details tab of Configure SSIS Logs).
• Dts.Events.FireWarning - raises a warning (to turn on its logging, enable OnWarning setting on the Details tab of Configure SSIS Logs).
• invoking EventLog.WriteEntry method of System.Diagnostics namespace (as documented in the MSDN Library). Interestingly, this approach is independent of the settings defined within the Configure SSIS Logs dialog box, allowing you to record arbitrary entries during task execution in a log of your choice.

In our sample code, we include examples demonstrating the use of Dts.Events.FireInformation, Dts.Events.FireError, and EventLog.WriteEntry (make sure to enable their logging on the Details tab of the Configure SSIS Logs dialog box if you want to implement them), but you could just as easily employ other methods to record their respective events. Since we also intend to take advantage of three system variables (System::TaskName, System::PackageName, and System::ExecutionInstanceGUID) to indicate the source of events registered in the Application log, designate them as ReadOnlyVariables in the Script Task Editor interface. In addition, include the Imports System.Diagnostics statement in the (General)(Declarations) section of the Script Task code (to simplify the use of EventLog.WriteEntry method). Finally type in the following code within the Sub Main():

Public Sub Main()
'
' Add your code here
'
Dim strTask As String = Dts.Variables("TaskName").Value.ToString
Dim strPackage As String = Dts.Variables("PackageName").Value.ToString
Dim strInstanceGUID As String = Dts.Variables("ExecutionInstanceGUID").Value.ToString
Dim strSource As String = strPackage + ":" + strTask + ":" + strInstanceGUID
 
Dts.TaskResult = ScriptResults.Success
 
   Try
        EventLog.WriteEntry(strSource, "Starting the Script Task", EventLogEntryType.Information)
        Dts.Events.FireInformation(0, strTask, "Informational Event", String.Empty, 0, False)
        Dts.Variables.Add("InvalidVariable", False, "", Nothing)
   Catch ex As Exception
        EventLog.WriteEntry(strTask, ex.Message.ToString(), EventLogEntryType.Error)
        Dts.Events.FireError(-1, strSource, ex.Message.ToString, String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
   End Try
 
End Sub

As you can see, we start by setting up variables that will identify the source of events appearing in the Application log when invoking the EventLog.WriteEntry method. We also fire an informational event, resulting in an additional entry of the same type appearing in the Application Log. Next, we intentionally trigger an error by referencing a non-existing SSIS variable. We employ the EventLog.WriteEntry method again, but this time to record the error and its exception data. Similar entry (although with Data Code parameter set to -1) is generated by Dts.Events.FireError method. As expected, this error actually appears in the log one more time, as the consequence of default SSIS Script Task On Error logging, which we enabled earlier. Finally, the package itself reports the failure (with SQLISPackage100 as the event source).

» See All Articles by Columnist Marcin Policht