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.
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.
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.
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.
More information on using event handlers to do custom logging can be found here .