SSIS Catalog: Part 5 – Logging and Execution Reports

In previous blog post of SSIS Catalog series, I talked about how to execute a package, and what are tables in SSISDB which store information about execution of package. 

In this post I will talk about logging in SSIS 2012, and different logging options and how we can set logging level at the time of execution.

Logging

Enabling logging in SSIS 2012 is much easier than earlier versions, you don’t need to edit package in development time in SSDT, the only action you should do is to set logging level at the time of execution.

As you can see in the execute package window you can simply set Logging Mode to one of these values: None, Basic, Performance and Verbose.

Each logging mode supports logging of specific events at the time of package execution. in list below you will see different logging modes and what events they log.

None

Basic

Performance

Verbose

No events

OnError

OnInformation

OnPostExecute

OnPostValidate

OnPreExecute

OnPreValidate

OnTaskFailed

OnWarning

OnError

OnWarning

BufferSizeTuning

Diagnostic

DiagnosticEx

OnCustomEvent

OnError

OnInformation

OnPipelinePostEndOfRowset

OnPipelinePostPrimeOutput

OnPipelinePreEndOfRowset

OnPipelinePrePrimeOutput

OnPostExecute

OnPostValidate

OnPreExecute

OnPreValidate

OnProgress

OnTaskFailed

OnWarning

PackageEnd

PackageStart

PipelineComponentTime

PipelineExecutionPlan

PipelineExecutionTrees

PipelineInitialization

SSIS Catalog Reports

SSIS execution tables store information about execution of package with detailed information (based on selected Logging mode at the time of execution). SSIS 2012 provided some Reporting Services reports which helps administrators and support of packages.

There are two kind of reports; Standard reports which are some pre-designed reports (Executions, Validations, Operations and Connections), and the other type of reports are reports that you develop yourself and open them with Custom Reports option in SSMS.

Integration Services Dashboards shows an overall information about packages, messages and you can click on packages or messages to get more details in other reports.

Execution report shows information about packages executed, their start and end time, their current status, how long each step in execution took to finish, different events and their messages, error and warnings …

For each package you can see an overview report which shows information about the package and execution details like screenshot below:

Execution Performance shows a graph of total execution time of a package and each point of graph shows an instance of execution. you can simply track execution time of package as an administrator and perform appropriate action if execution time raising by the time.

Queries

You can also write queries to run on queries on execution tables (list of execution tables and their detailed information listed here).

This is an example of queries which shows information about data flow components and number of rows sent during each output:

In next blog post I will talk about operations and some helper tables to get detailed operation log results from your t-sql queries on SSISDB.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply