How do I log to multiple locations?

How do I log to multiple locations?

Rate This
  • Comments 2

Problem: I want all errors in my package to be reported to the Windows Event Log, and all other events to be logged to SQL Server.

Logging all messages to multiple locations is pretty simple. On the Configure SSIS Logs dialog (SSIS –> Logging …), you can add any number of loggers.

image

The tricky part of this problem is that the user wants to only log error messages to the Windows Event Log, while still receiving all of the other messages on your other log provider. The Details tab of the Configure SSIS Logs dialog lets you select individual events, but unfortunately, this applies to all loggers in the package.

image

There are a couple of solutions here.

Run the Package Programmatically

By running the package using the SSIS object model, you can capture all events through the IDTSEvents interface. This gives you complete control over what to do with the log messages, and which events you want to intercept. DTLoggedExec is a great example of what can be done with this approach.

Use a Script Task

In this approach, we’ll add an event handler to the package’s OnError event, and use a script task to write directly to the EventLog.

First, I add a new event handler, and drag on a script task. Note that because of the way SSIS event propagation works, we only need one event Handler at the Package level to capture all errors in the package.

image

The OnError event handler has a set of System Variables that we will access to create our event log message.

Name Description
System::ErrorCode The error code included with the original error event
System::ErrorDescription The error description
System::EventHandlerStartTime The time the error event occurred (or close enough for logging purposes)
System::SourceID The GUID of the task that raised the event
System::SourceName The name of the task that raised the event
System::UserName The name of the user account that invoked the SSIS package
System::ExecutionInstanceGUID The GUID for this package’s execution
System::PackageID The GUID for the package
System::PackageName The name of the package

The script task code looks like this:

public void Main()
{
    try
    {
        int errorCode = (int)Dts.Variables["System::ErrorCode"].Value;
        string errorDescription = (string)Dts.Variables["System::ErrorDescription"].Value;
        DateTime handlerStartTime = (DateTime)Dts.Variables["System::EventHandlerStartTime"].Value;
        string sourceId = (string)Dts.Variables["System::SourceID"].Value;
        string sourceName = (string)Dts.Variables["System::SourceName"].Value;
        string userName = (string)Dts.Variables["System::UserName"].Value;
        string executionId = (string)Dts.Variables["System::ExecutionInstanceGUID"].Value;

        const string EventSource = "SQLISPackage100";
        const string Log = "Application";

        // Build up the error message
        StringBuilder sb = new StringBuilder();
        sb.Append("Event Name: OnError\n");
        sb.Append("Message: ");
        sb.Append(errorDescription);
        sb.Append('\n');
        sb.Append("Operator: ");
        sb.Append(userName);
        sb.Append('\n');
        sb.Append("Source Name: ");
        sb.Append(sourceName);
        sb.Append('\n');
        sb.Append("Source ID: ");
        sb.Append(sourceId);
        sb.Append('\n');
        sb.Append("Execution ID: ");
        sb.Append(executionId);
        sb.Append('\n');
        sb.Append("Time: ");
        sb.Append(handlerStartTime);
        sb.Append('\n');
        sb.Append("Error Code: ");
        sb.Append(errorCode);

        EventLog.WriteEntry(EventSource, sb.ToString(), EventLogEntryType.Error);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {
        Dts.Events.FireError(0, "OnError", "Failed to log error message", null, 0);
        Dts.Events.FireError(0, "OnError", e.Message, null, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

In this example the error message I’m outputting looks similar to the default one issued by the SSIS log provider for Windows Event Log, but you can customize the text to whatever format you’d like.

image

More information on using event handlers to do custom logging can be found here.

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Nice article thanks, but it would be better if you mention the references (using System.Text;

    ,using System.Diagnostics;)

  • Thanks for the post ...Could have shared about all types of logging ...

Page 1 of 1 (2 items)