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 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.