The best and fastest way to track down issues with an SSIS package is with effective logging. While logging is a feature of standard SSIS packages as part of SQL Server, your may want to take your logging to the “next level”. In this article by Hans Michiels, we learn how to add some advanced logging and how to use the new features to get to the “next level” of logging.
The features in short (as compared with what SSIS offers out of the box):
- It’s a Plug And Play SSIS Logging Solution in most cases: no additional programming in SSIS Packages is required, so can be easily used for all existing packages! Only when you have multiple master packages with the same name, you will have to do a little of set up work.
- Log messages are labeled with a message class (a sort of severity of the message) for easy filtering.
- Changing the maximum message class to log is possible without redeploying any SSIS Packages (for instance enable VERBOSE logging for trouble shooting, then set it back to DEBUG or INFO).
- Log messages are labeled with a retention class for easy purging. There is also an “audit” retention class for messages that may never be purged/deleted.
- Purging can be automated, so no scheduling is needed.
- Logging is Integrated/centralized for SSIS Packages, stored procedures and other home made software.
- Deduplicated logging can be enabled, if this is done, identical SSIS logging messages (e.g. first from the OLE DB Destination, then from the Data Flow Task, and then from the SSIS Package) are logged only once as much as possible.
- Master / child relations for SSIS packages can be configured, so that log entries from master- and childpackages can be retrieved easily together in a single timeline. Out of the box this works using a naming convention (Master package must have “master” in the name) but can be fine tuned for every package that runs.
- Easy configuration by executing a stored procedure, to set retention periods, maximum log class, automatic purging on/off, deduplicated logging on/off, and naming convention for the master package.
- Storage of both start- and endtimes of the current timezone as well as UTC start- and endtimes.