Previously I had blogged about new project deployment model available in SSIS 2012 in one of my groups blogs. This is a follow up post giving details on monitoring options for package execution in SSIS 2012.
Till SQL 2012 we didn’t have a good way to monitor the execution status of SSIS package jobs unless you implement some custom logging on SQL Server table and then build our own custom reports to pull the data from log tables. There have been lots of occasions where we had developed similar reports using SQL reporting services for use by the operations team.
In SQL 2012 with the introduction to project deployment model and Integration Services catalog we now have a set of standard reports available through SQL Server Management Studio to fetch the execution status of packages. This comes handy in occasions where we’ve scheduled packages to run as SQL agent jobs. We will look at some of reports among them and see what all details they provide
The main reports among them are
Till SQL 2012 we didn’t have a good way to monitor the execution status of SSIS package jobs unless you implement some custom logging on SQL Server table and then build our own custom reports to pull the data from log tables. There have been lots of occasions where we had developed similar reports using SQL reporting services for use by the operations team.
In SQL 2012 with the introduction to project deployment model and Integration Services catalog we now have a set of standard reports available through SQL Server Management Studio to fetch the execution status of packages. This comes handy in occasions where we’ve scheduled packages to run as SQL agent jobs. We will look at some of reports among them and see what all details they provide
The main reports among them are
1. All Executions report
this reports gives details of all package executions in a server instance. The report looks like below
The report consists of an overview part giving count of packages based on execution status like failed,executing etc. this is followed by details of each execution giving ID,Execution Status, Log report, etc. You may apply filter to report through filter screen to restrict log to be within a date range, based on status values,project etc to filter results. The report even gives you drilldown options on message,overview and execution performance to generate corresponding reports as below
Each of these reports provides a different view on the execution log of packages in various formats giving users a clear understanding on the execution history. This gives the operations team an easier method to get much better idea on job execution history compared to previous versions where it just used to give error as a simple message without proper representation in any form.
Other major types of available reports are as given below
2. All Operations report
This reports as description says gives an overview on integration services operations like start time, end time,duration etc for each of execution of packages in SSIS catalog
3. Integration Services Dashboard
The dashboard accumulates lots of information regarding package execution and is the main report which links to all the other reports. This is a kind of high level reports which gives overall overview on package job operation in last 24 hrs time period.
All of these reports are reporting services reports and make use of data available in system tables and catalog views available in SSISDB. We can even extend this to include our own reports and include them to be accessed from Management Studio.You need to develop the reports locally using SSDT and browse and open them in SSMS to get them included along with standard pack.
