How to Have Standard Logging in SSIS and Avoid Traps

As the number of developers and/or packages increase in a team, it becomes difficult to ensure that everyone is logging a common minimum number of events in the packages. The common minimum could be necessary for consistency in reports and general analysis. Documentation, checklists and trainings are helpful but the easiest way to ensure a common logging implementation would be a logging template.

I am assuming that you are already familiar with the concept of event logging in SSIS to monitor execution status. This post is not going to be a beginners level introduction to logging. I will rather discuss options to have a minimum standard logging of events across packages and teams with minimal effort. I’ll also mention some traps to avoid.

I am using a demo package with two Data Flow Tasks and a Execute SQL Task. I have enabled logging for the first few events at the package level for the sake of demonstration. The logging configuration options for the package node are shown in the first image.

Image 1 – Logging configuration window for the package node

The logging options at the child container node Data Flow Task 1 appears as in the second image. The configuration for other Data Flow and the Execute SQL Task look the same.

Image 2 – Logging configuration window at the child container node

The check marks for the tasks are grayed out which means they are inheriting the logging options from their parent, i.e. the package. To disable logging for a task, remove its check mark in the left tree view window. Logging can also be disabled by going to the Control Flow canvas and changing the LoggingMode property of the task to Disabled.

The Trick

Now look at the bottom of the images again. Notice the Load… and Save… buttons? They do exactly what they say. You can set your logging options and save them as an XML template. Later, this XML template can be loaded into other packages to enable the same logging options.

The XML template file has nodes for each event. For example, the logging options for OnError event are saved like this –

-<EventsFilter Name="OnError">

-<Filter>;

<Computer>true</Computer>

<Operator>true</Operator>

<SourceName>true</SourceName>

<SourceID>true</SourceID>

<ExecutionID>true</ExecutionID>

<MessageText>true</MessageText>

<DataBytes>true</DataBytes>

</Filter>

</EventsFilter>

Notice that the XML just mentions the event name, not the name of any task. This means that when the template file is loaded, this logging option will be set for any task where the event is applicable. More on this later.

The Traps

The OnError event is a generic event applicable to all tasks. Lets talk about events that are specific to tasks. For example, the BufferSizeTuning event is applicable just to the Data Flow Tasks, not Execute SQL Tasks.

When I proceed to set logging for BufferSizeTuning event, I have to set it individually in the Data Flow Task tree node. Notice the message at the bottom of the second image that says “To enable unique logging options for this container, enable logging for it in the tree view.”. This message is important in the context of saving and loading a template file too. When I save a template file, the logging options of just that tree view node are saved. For example, the BufferSizeTuning event will be saved in the template only if I am at the Data Flow task in the tree view. It will not be saved if I am at the Package or the Execute SQL task in the tree view.

The reverse is also true. When I load a template, its logging options are applied to just that node which I select in the tree view. For example, if I load a template at the Data Flow Task 1, the options will not be applied to the Data Flow Task 2 or the Execute SQL Task. If the template has an event that is not applicable to the task then that event’s settings will be ignored. For example, while loading a template file to an Execute SQL Task, even if the template has BufferSizeTuning event logging options, it will just load options for the events relevant to the Execute SQL Task. The BufferSizeTuning event will be ignored because this event is only meant for the tasks of Data Flow type. The fact that non-relevant options are ignored can be helpful for us to consolidate all logging options in a single template file.

Conclusion

A package level Save and Load of a logging template is straight forward. But if you need to have logging for events that are specific to a task type, then consider creating a logging template for each type of task. Also, if your logging configuration requires anything else than a package level settings, remember to load the template for each task in the tree view.

Number of Template Files	How	Pros and Cons
Individual File per Task	Create one template file for each type of task. The file will have events applicable to that task.	Pros – Easier to know what type of tasks have a template and which ones do not. Cons – More files to manage.
Single File for All Tasks	Create a template file for each task. Then copy all event options in a single XML file.	Pros – One file is easier to manage. Cons – Not obvious which tasks are include. Need to put in comments in the XML file.