This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
- Variables and Parameters
- SSIS Expressions
- Transactions and Checkpoints
Last time I introduced the use of transactions and checkpoints as ways to help handle errors and failures in your package execution. The other part of the execution equation is logging. Implementing logging in your SSIS packages not only helps you debug runtime errors, it can also help you monitor overall performance, resource utilization, and, well, exactly what your package is doing at a given point. When implementing logging in SSIS, there are some big questions you need to ask yourself.
What information do I need?
Let’s start out with the biggest question: what information do you need to capture in your log? This is really going to depend on how the log is intended to be used. Are you implementing logging as a means to debug execution failures? If so, then you probably only need to capture execution boundary events (e.g. OnPreExecute, OnPostExecute) and, of course, execution exception events (e.g. OnError, OnTaskFailed). On the other hand, SSIS logging can be useful in monitoring performance and resource utilization in your packages. In this case, you may wish to add execution progress events (e.g. OnProgress, OnVariableValueChanged) for more granularity. By marrying your SSIS logs with Performance Monitor logs, you can get a pretty good picture of resource utilization throughout execution.
Once you’ve determined which events you want to capture in your log, you then need to decide what information about those events is important. You want to ensure that the data you’re collecting is going to be sufficient for your intended purpose. For any SSIS event, the following properties are available:
- Computer – the name of the computer where the event occurred
- Operator – the name of the user who executed the package
- SourceName – the name of the container or task where the event occurred
- SourceID – the GUID of the container or task where the event occurred
- ExecutionID – the GUID assigned to each execution of a package
- MessageText – the message associated with the event
- DataBytes – a byte array specific to the event
- StartTime – the execution start time of the task or container
- EndTime – the execution end time of the task or container
- DataCode – the result of the task or container (0=success, 1=Failure, 2=Completed, 3=Cancelled)
When deciding what events and event properties to log, keep in mind that logging generates overhead. The more you log, the more overhead you’ll incur. So consider this question carefully before deploying any SSIS logging.
Where can SSIS log to?
Once you’ve determined what you need to log, the next thing you need to decide is where you want to keep this information. The destination for log output in SSIS is called a log provider, and you’ve got five options:
- SQL Server – events are recorded in the sysssislog system table in a SQL Server database
- SQL Server Profiler – events are recorded to a trace file that can be opened in Profiler later (this option is only available if running a package in 32-bit mode)
- Text file – events are written to a comma-delimited text file
- Windows Event Log – events are written to the Windows Application log
- XML File – events are written to an XML file
Which log provider is right for your package will, again, largely depend on how you intend to use the logged info. If your intention is to log errors/failures, then you (hopefully) shouldn’t expect too much logging and you might opt to use the Windows Event Log or maybe a text file. However, when monitoring performance and resources in your package, using the SQL Server Profiler option allows you to easily align your log output with recorded Perfmon counters from within Profiler. Storing log data in SQL Server offers the ability to query and analyze output, but XML files or text files offer great portability.
One thing to keep in mind when selecting a log provider is availability. Ideally, your logging solution should be highly available and resilient, and should not fail, even if your package does. SSIS allows you to use multiple log providers simultaneously for better redundancy.
How is logging configured?
To implement logging in an SSIS package, begin by selecting Logging from the SSIS menu, or right-clicking on your package designer surface and selecting Logging. This will open the Configure SSIS Logs window. In the Containers tree view, you’ll see all of the objects in your package in a hierarchical tree. SSIS logging respects the hierarchy of objects in your package, so configuring logging on a container will also configure logging for all tasks inside that container, as long as their LogMode property is set to UseParentSetting (the default). Select the container or tasks you’d like to configure via the checkboxes.
If there are already logs configured in the project, you’ll see them listed in the Providers and Logs tab. You can select an existing log, or create a new one by selecting a type of log provider from the Provider Type drop down, and clicking the Add… button to add it to the list. You may then rename the log and edit its description, but one you must do is edit the Configuration field to a valid Connection Manager. When you’re done configuring any new logs, select the log(s) you want to use and go to the Details Tab.
On the Details tab, you’ll see the various events available for logging. Click the checkbox for each desired event. Clicking the Advanced>> button displays the optional properties for each event. You can customize which properties are recorded for each selected event. You’ll notice that StartTime, EndTime, and DataCode are not listed. This is because these properties are automatically captured for every event.
There are two more buttons at the bottom of the Details tab: Load… and Save… Since logging is an important part of SSIS package development and deployment, you’ll likely want to configure logging on most, if not all of your packages. And having a standard logging methodology across a solution, department, or organization, just makes sense. To help with this, SSIS offers the ability to save and reuse log configuration templates. Once you’ve configured a logging solution you want to use as your standard, click the Save button and save the configuration as an XML file. The next time you want to reuse that template in a package, click the Load button from this window and select your template.
Additional resources
For more information on implementing logging in SSIS, check out the following resource:
