SQL Server 2012 (“Denali”): SSIS Logging

In previous SSIS versions as the developer you were responsible for configuring all of your logging options, such as what events you were going to log, which containers you were going to collect logs for, and where you were going to store those logs.  These logs would collect info such as which packages where executed, how long they took, error messages, rows processed, etc.

In SSIS for SQL Server 2012 that all goes away.  The SSIS server is responsible for managing all of your deployed projects/packages and in turn manages both the execution of them and the collection of log information.  Instead of having lots of options for logging we now have a definitive one and over time it is the one that everyone will use.

In SQL Server 2012, the SSIS catalog is the central storage and administration point for SSIS projects, packages, parameters and environments, and handles all the logging.  This catalog is part of a new and optional SSIS deployment model called the  “Project Deployment Model” along with traditional “Legacy Deployment Model”.

SSIS catalog automatically captures logs for packages run on the server.  The logging level is set to Basic by default, and can be set higher to capture more info.  The data in stored in twenty-five different views under the “catalog” schema (in the SSISDB database which you manually create), and contains a ton of info.  Jamie Thomson does a great job covering these views at SSIS Logging in Denali.

SSMS also comes with built-in reports that use the catalog views, or you can create your own.

More info:

SSIS Reporting Pack

SSIS Reporting Pack – High Level Review

Views (Integration Services Catalog)

Setting a Default Logging Level in the SSIS Catalog

What Events are Included in the SSIS Catalog Log Levels

Report Authoring on the SSIS Catalog

Video What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration Services

Is the SSIS Catalog going to be enough?

T-SQL Tuesday 31: SSIS 2012 Logging

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server 2012, SQLServerPedia Syndication, SSIS. Bookmark the permalink.

One Response to SQL Server 2012 (“Denali”): SSIS Logging

  1. Pingback: Jumping from SSIS 2008 to SSIS 2012 | James Serra's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>