This is the eleventh article in a series entitled Stairway to Integration Services. Previous articles in the series include:

• What is SSIS? Level 1 of the Stairway to Integration Services
• The SSIS Data Pump - Level 2 of the Stairway to Integration Services
• Adding Rows in Incremental Loads – Level 3 of the Stairway to Integration Services
• Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services
• Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services
• Basic SSIS Workflow Management – Level 6 of the Stairway to Integration Services
• Intermediate SSIS Workflow Management – Level 7 of the Stairway to Integration Services
• Advanced SSIS Workflow Management – Level 8 of the Stairway to Integration Services
• Control Flow Task Errors – Level 9 of the Stairway to Integration Services
• Advanced Event Behavior – Level 10 of the Stairway to Integration Services

Introduction

In the previous installment, we discussed the behavior of events. We shared methods for manipulating the default behavior of event bubbling and introduced the Parent-Child pattern.

In this article, we will configure SSIS’ built-in logging. We will demonstrate simple and advanced log configuration, storing and retrieving log configurations, and generating custom log messages.

More About SSIS Task Events

Open the Precedence.dtsx package. Your Control Flow should appear as shown in Figure 1:

Figure 1

As in Steps 9 and 10, we focus on Sequence Container 1.

In Step 9 of this series, we created OnError event handlers – which we described as event “listeners” – on Script Task 4 and Sequence Container 1. In Step 10 of this series, we extended the pattern by adding an OnError event handler for the Precedence.dtsx SSIS package. To each OnError event handler, we added a Script Task that displays a message box containing the values of the following SSIS OnError event handler variables:

·         System::ErrorCode

·         System::ErrorDescription

·         System::SourceName

Before we begin testing, let’s verify and/or change some settings from the previous build of the Precedence.dtsx SSIS package. First, click anywhere in the whitespace of the Precedence.dtsx SSIS package Control Flow and then press the F4 key to display Properties. Change the DisableEventHandlers
property to False as shown in Figure 2:

Figure 2

Before we make any more changes, execute the Precedence.dtsx SSIS package in the BIDS (Business Intelligence Development Studio) debugger by pressing the F5 key. Your response to the prompt “Succeed Script Task 2?” does not matter for the purposes of demonstration, but now and throughout this article we will opt to click the Yes button shown in Figure 3 to cause the Script Task to succeed:

Figure 3

When prompted to “Succeed Script Task 4?” – as shown in Figure 4 – click the No button to raise an Error event from Script Task 4:

Figure 4

When Script Task 4 raises an Error event, the OnError event handler we configured in Step 9 “hears” the Error event and executes, displaying a message box similar to that shown in Figure 5:

Figure 5

But the Error event doesn’t stop there, it continues to bubble up in scope – traversing the execution stack to the next level: Sequence Container 1, which contains Script Task 4. We see a similar message
box displayed from the OnError event handler configured on Sequence Container 1 shown in Figure 6:

Figure 6

Again, the Error continues to bubble to the next level: the Precedence.dtsx SSIS package, which contains Sequence Container 1. We see a similar message box displayed from the OnError event handler
configured on the Precedence package shown in Figure 7:

Figure 7

Once we acknowledge this message box, another message box displays informing me that Script Task 3 has completed. Acknowledging the Script Task 3 message box allows the Precedence.dtsx SSIS package to
complete execution in the BIDS debugger, and it appears as shown in Figure 8:

Figure 8

Event Listeners

Tasks and containers in SSIS are referred to as executables, including the SSIS package object. Executables raise events. SSIS event handlers are “listeners” that respond to events when events are raised by tasks and containers.

Think of SSIS events as messages sent between executables. The messages are transmitted according to rules. One first rule is: Messages flow from lower to higher scope.

SSIS logs are also listeners.

Configuring SSIS Logging

To configure SSIS logging, click the SSIS dropdown menu and click Logging as shown in Figure 9:

Figure 9

The Configure SSIS Logs window displays, shown in Figure 10:

Figure 10

The available log provider types are:

• Windows Event Log
• Text Files
• XML Files
• SQL Server
• SQL Server Profiler

Select the Text File provider as shown in Figure 11:

Figure 11

Once “SSIS log provider for Text files” is selected, click the Add button to add a Text file log to the Precedence.dtsx SSIS package, as shown in Figure 12:

Figure 12

There is a really important note at the bottom of the Configure SSIS Logs window. It instructs us, telling us what we need to do next, and is shown in Figure 13:

Figure 13

As stated in the message shown in Figure 12, the next step is to enable logging by selecting a checkbox in the Containers treeview. Enable SSIS logging for the Precedence package, as shown in Figure 14:

Figure 14

We can now configure the log itself. The first thing we need to do is assign the log to the package selected in the Containers treeview. We assign the log by checking the log checkbox as shown in Figure 15:

Figure 15

Once the log checkbox is checked, you can edit the Name and Description properties. The Configuration property is required. Click the dropdown in the Configuration column and click “<New connection…>” as
shown in Figure 16:

Figure 16

A new File Connection Manager is created in the Connection Managers and the File Connection Manager Editor is opened, as shown in Figure 17:

Figure 17

This File Connection Manager will be used to configure the file used by the Text File log. Select “Create file” from the Usage Type dropdown. The log file will not be created each time the SSIS package is executed; it will be created if it doesn’t exist. If the log file exists, log data will be appended.

When the Select File window appears, navigate to the My_First_SSIS_Project solution folder and create a new folder named “log” as shown in Figure 18:

Figure 18

Open the new log folder and type the file name “MyLog.csv” as shown in Figure 19:

Figure 19

Click the Open button to complete the file configuration as shown in Figure 20:

Figure 20

Click the OK button to complete the File Connection Manager configuration. Your Connection Managers tab should appear similar to that shown in Figure 21:

Figure 21

We have configured a text file log for the Precedence.dtsx SSIS package. Execute the package in the BIDS debugger, then open MyLog.csv in Notepad.

Remember, SSIS logs are listeners that “hear” events. By default, the log listener hears the PackageStart
and PackageEnd events, as shown in Figure 22:

Figure 22

Adding Events

Click the SSIS dropdown menu and then click Logging. When the Configure SSIS Logs window displays, click the Details tab. This list looks familiar. It’s the list of events we encountered back in Step 9, Figure 16. If you recall, all executables raise events. The listeners – SSIS event handlers and SSIS logs – access the collection of executable events in the SSIS package. If we add different tasks to the SSIS Control Flow, we will see additional events in this list and in the list of event handlers.

Select the OnError and OnInformation events, as shown in Figure 23:

Figure 23

Execute the Precedence.dtsx SSIS package in the BIDS debugger, succeeding Script Task 2 and failing Script Task 4. Open MyLog.csv and examine the file, shown in Figure 24:

Figure 24

The first entry that follows the original PackageEnd event is a new PackageStart event, demonstrating the log file is created if it does not exist and appended if it does exist. The next three messages are OnError events. Here we see another representation of event bubbling as the first recorded OnError event is raised by Script Task 4, followed by an OnError event from Sequence Container 1, followed by an OnError event from the Precedence.dtsx SSIS package. The last event recorded is another PackageEnd event.

Advanced Log Configuration

Open the Configure SSIS Logs window for the Precedence.dtsx SSIS package and click the Details tab. There are three buttons near the lower part of the Details tab: Advanced, Load, and Save; as shown in
Figure 25:

Figure 25

Click the Advanced button to display SSIS logging fields available for configuration. Figure 26 shows all fields are selected for the OnError and OnInformation events we selected earlier:

Figure 26

Let’s make some changes. Uncheck the ExecutionID column for the OnError and OnInformation events. Uncheck the DataBytes column for the OnError event and the SourceID column for the OnInformation event. Your advanced logging configuration grid should now appear similar to that shown in Figure 27:

Figure 27

Before we leave the advanced logging configuration screen, click the Save button. When the Save As dialog displays, enter MyLogConfig in the File Name textbox as shown in Figure 28:

Figure 28

Click the Save button to store this advanced logging configuration in an XML file. To demonstrate how this works, clear the advanced logging configuration grid so it appears as shown in Figure 29:

Figure 29

Click the Load button. When the Open dialog displays, select the MyLogConfig file and click the Open button, as shown in Figure 30:

Figure 30

Note the advanced logging configuration grid returns to the selections saved (see Figure 26). You can use this functionality to encourage developers in an enterprise to collect similar logging fields from SSIS’ built-in logging.

Click the OK button to close the Configure SSIS Logs window.

Raising Custom Events On-Demand, Part 1

There are quite a few logging messages generated automatically by SSIS. But you can also manually raise events that can then be “heard” by the log, thereby generating custom log messages.

To demonstrate, open Script Task 3’s editor and click the Edit Script button. The code in Public Sub Main() should appear similar to that shown in Listing 1:

      Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        MsgBox(sTaskName & " completed.")
        Dts.TaskResult = ScriptResults.Success
      End Sub

Listing 1

Edit the script so it appears as shown in Listing 2:

Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim sMsg As String = sTaskName & " completed."
        Dts.Events.FireInformation(101, sTaskName, sMsg, "", 0, True)
        MsgBox(sMsg)
        Dts.TaskResult = ScriptResults.Success
End Sub

Listing 2

We first added a variable named sMsg (String) and assign it the value sTaskName & " completed." Next, we add code to manually raise an Information Event: Dts.Events.FireInformation(101, sTaskName, sMsg, "", 0, True). The Dts.Events object can raise many different types of events, including the Error and Information events for which our current log configuration is listening. FireInformation has six arguments: informationCode (Integer), subComponent (String), description (String), helpFile (String), helpContext (Integer), and fireAgain (Boolean). InformationCode can be used to categorize messages. I use subComponent to identify the task raising the event. The description is the message we desire to log. HelpFile and helpContext are used to link the message to Help topics. I have never configured these topics, so I am not certain how (or if) they work. As far as I know, FireAgain is obsolete – I set it to True by default. The last change I made to the script was to replace the message delivered by the MsgBox statement to the sMsg variable built earlier in the script.

Close the ssisscript editor and click the OK button to close the Script Task Editor. Execute the Precedence.dtsx SSIS package in the BIDS debugger, succeed Script Task 2, fail Script Task 4, and acknowledge the Script Task 3 completion message box.

Open the log file and examine the latest messages recorded, as shown in Figure 31:

Figure 31

Note the new OnInformation event – which also bubbles – starting with Script Task 3, then Sequence Container 1, and then the Precedence.dtsx SSIS package. The full text of the OnInformation event log entry for Script Task 3 is shown in Listing 3:

OnInformation,ANDYI7,AndyI7\A. Ray Leonard,Script Task 3,,,9/23/2012 5:32:45 PM,9/23/2012 5:32:45 PM,101,0x,Script Task 3 completed.

Listing 3

Raising Custom Events On-Demand, Part 2

If we examine the full error message from the log file, we see a message similar to that shown in Listing 4:

OnError,ANDYI7,AndyI7\A. Ray Leonard,Script Task 4,{3f1daa8b-d647-4a5b-8837-cdd8a4fe36bc},,9/23/2012 5:32:43 PM,9/23/2012 5:32:43 PM,-1001,0x,Script Task 4 failed!

Listing 4

Similar to Information events, we can raise custom Error events. To demonstrate, open Script Task 4’s editor and click the Edit Script button. We see this in action already in the code from Script Task 4’s Public Sub Main() shown in Listing 5:

Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim iResponse As Integer
        iResponse = MsgBox("Succeed " & sTaskName & "?", _
                           MsgBoxStyle.YesNo + MsgBoxStyle.Question, _
                           sTaskName & " Success Question")
        If iResponse = vbYes Then
            Dts.TaskResult = ScriptResults.Success
        Else
            'Dts.TaskResult = ScriptResults.Failure
            Dts.Events.FireError(-1001, "Script Task 4", _
                                 "Script Task 4 failed!", "", 0)
        End If
End Sub

Listing 5

The message shown in Listing 4 is being generated by the Dts.Events.FireError method shown in Listing 5.

Conclusion

In this article, we configured SSIS’ built-in logging, demonstrated simple and advanced log configuration, stored and retrieved log configurations, and generated custom log messages.