SSIS Catalog: Part 5 – Logging and Execution Reports

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply