SSIS Catalog DB Dashboard

Just noticed that changing servers can cause the Execution to Date Join to be dropped.

Just add it back from ExecutionStartDate to Date

This looks like a great way to monitor jobs, thanks for posting. I have downloaded the "SSISDB_Dashboard_v0_81_Full_Blank.pbix" version and can change the msdn connection from localhost to the sql instance that we are looking to monitor. However I cannot find a way to modify the SSISDB connection. The Edit Connection button greys out when I select it.

Any assistance would be greatly appreciated.

Thanks

This has been a very helpful dashboard. How would you interpret the scatterchart on the 'Execution Gantt' page with StartOrderRev on the y-axis and Step Order End on the x-axis?

Big new version so I've updated the screens and doing a full description again.

I use SSIS extensively with my job and over the years have used various tools to help me manage the packages and triage issues.
These include my own SSIS logs, the out of the box SSMS reports, Jamie Thompson's report pack and a number of SSIS catalog queries.

I had a quite good single query SSIS PowerBI Dashboard, but it was slow on large catalogs and limited in its use.

I started working on new dashboard that provided all of the features from all my past tools and around the time I saw a post from Chris Schmidt (link below) and so decided to use this as a starting point.

My dashboard is still not finished, but after lots of iterations I think it's going to be useful for others who need to monitor and understand what their SSIS packages are doing.

I first posted this a in 2017 and I've just given it an overhaul to support some of the new feature now supported by the August 2018 Desktop & Report server versions. Please give it a try and let me know if you spot any problems or things that could be improved.

I bring in and summarise the data is at a variety of levels

Execution (Execution & Execution Summary) The Package that is Executed First which I've named RootPackageName. This includes the Project & Folder
Package (Executable Package Stats) Package Summary. I group Packages by Types e.g. Master/Dimension/Fact/Stage/PostProcess. You can customised these in the PackageTypes Table. Click Advanced Editor to change it.
Executable (Executable Stats) Items inside the Package e.g. Tasks/Data Flows/Sequence. I also call this level Package Steps
I categorise packages and task into types using a keyword lookup. e.g. Package starting Fact for loading Fact tables.

The Task type names are based on common SSIS naming conventions.

This dashboard also provides details of any package errors and maps them to the Microsoft IS Error Reference Names.

Individual Executions can be viewed as a text based Gantt chart or as a matrix of executions by time slices.

Finally I extracted the Hierarchy from the execution paths to make a Network diagram and Sankey to visualise the structure and levels in an SSIS project.

Whilst developing this dashboard I managed to combine and try out a number PowerBI techniques such as;

Dax Measure to display a Gantt chart (Based on Chris Webb's Star Ratings)
Turn Package Paths into a Network diagram & Sankey Diagram
Scatter Chart to show hour and day matrix
Mini Gantt Timeline with Matrix
Mini Gantt Timeline with Scatter Chart
Dax Tables for Summary data
Vlookup function to use Package names to provide a type. This can do contains matches and use Start and End Indicators.
M Language tables for reference data
Parameterised SQL to Configure the Days to Load
Standard Deviation for tolerances.

Bookmarks & Selections

Report Tooltips

I've tried to keep the custom visuals to a minimum but I have used Sankey/Network Navigator as well as the OKViz Bullet and Sparklines.
 
The PowerBI is blank with that needs the server parameter setting to your SSIS db server before applying the changes and granting permissions.

Templates and sample screen shots are available here https://github.com/stretcharm/SSIS-DB-Dashboard

Release Notes

v1.00    
New Layout with Filter and Help Screens and Visualisation Toggles
Conditional Formating on Execution List
Add Execution Step Gantt and Project/Package Parameters
Report Tooltip on Gantt
Fix Duration if Package is called twice in an Execution
Add Option for different Server for SQL Jobs
Add TaskType Lookup based on naming convention
Add support for ^(start) and $(end) in Package Name and Task Type Lookups

Thanks to the providers of the following pages that I've used to help in the making of this dashboard.

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/04/18/ssisdb-reporting-with-power-bi/
Chris Schmidt

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/
KenPuls

Star Ratings Quick Measure
http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903#M12

cwebb

https://ssisreportingpack.codeplex.com/
Jamie Thomson

Lots of Dax help and great OK Vis PowerBI Visualisations
http://www.sqlbi.com/

SSIS Catalog DB
https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

Reza Rad's Article on the SSIS Catalog
http://www.rad.pasfu.com/index.php?/archives/75-SSIS-Catalog-Part-3-Folder-Hierarchy;-Folder,-Projects-and-Packages.html
DB Diagram
http://www.rad.pasfu.com/ssis/ssiscatalogpart3/5.png

SSIS Error Codes
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference

Enjoy

Phil

Hi stretcharm

Can you please share SQL code for Executions, Failed Executions, Package Steps, and Error Codes?

---

stretcharm wrote:

Hi,

This is not so much a Data Story but a template for a dashboard for looking at your own SSIS activity.

I use SSIS extensively with my job and over the years have used various tools to help me manage the packages and triage issues.
These include my own SSIS logs, the out of the box SSMS reports, Jamie Thompson's report pack and a number of SSIS catalog queries.

I had a quite good single query SSIS PowerBI Dashboard, but it was slow on large catalogs and limited in its use.

I started working on new dashboard that provided all of the features from all my past tools and around the time I saw a post from Chris Schmidt (link below) and so decided to use this as a starting point.

My dashboard is still not finished, but after lots of iterations I think it's going to be useful for others who need to monitor and understand what their SSIS packages are doing.

 

I first posted this a in 2017 and I've just given it an overhaul to support some of the new feature now supported by the August 2018 Desktop & Report server versions. Please give it a try and let me know if you spot any problems or things that could be improved.

I bring in and summarise the data is at a variety of levels

Execution (Execution & Execution Summary) The Package that is Executed First which I've named RootPackageName. This includes the Project & Folder
Package (Executable Package Stats) Package Summary. I group Packages by Types e.g. Master/Dimension/Fact/Stage/PostProcess. You can customised these in the PackageTypes Table. Click Advanced Editor to change it.
Executable (Executable Stats) Items inside the Package e.g. Tasks/Data Flows/Sequence. I also call this level Package Steps
I categorise packages and task into types using a keyword lookup. e.g. Package starting Fact for loading Fact tables.

The Task type names are based on common SSIS naming conventions.

 

This dashboard also provides details of any package errors and maps them to the Microsoft IS Error Reference Names.

Individual Executions can be viewed as a text based Gantt chart or as a matrix of executions by time slices.

 

Finally I extracted the Hierarchy from the execution paths to make a Network diagram and Sankey to visualise the structure and levels in an SSIS project.

 

Whilst developing this dashboard I managed to combine and try out a number PowerBI techniques such as;

Dax Measure to display a Gantt chart (Based on Chris Webb's Star Ratings)
Turn Package Paths into a Network diagram & Sankey Diagram
Scatter Chart to show hour and day matrix
Mini Gantt Timeline with Matrix
Mini Gantt Timeline with Scatter Chart
Dax Tables for Summary data
Vlookup function to use Package names to provide a type. This can do contains matches and use Start and End Indicators.
M Language tables for reference data
Parameterised SQL to Configure the Days to Load
Standard Deviation for tolerances.

Bookmarks & Selections

Report Tooltips

I've tried to keep the custom visuals to a minimum but I have used Sankey/Network Navigator as well as the OKViz Bullet and Sparklines.
 
The PowerBI is blank with that needs the server parameter setting to your SSIS db server before applying the changes and granting permissions.

The next network and rows can take a while to load wiht a large Catalog db so I have a small version that excludes these.

Templates and sample screen shots are available here https://github.com/stretcharm/SSIS-DB-Dashboard

 

Release Notes

v1.00   

Improved query on Exectutions and Executables

Added Include fields for the Package types to configure the Master & Package Page filtering.
Edit PackageTypesUse Query to configure.

Changed Package filters

Add Date, Start, Execution ID Hierarchy to the Master and Package Pages

Added Preserve Layers Bookmark to help avoid window coming to the front

Remove Optional Job Server Code
Add Icon Ribbon and Help Page.
Minor Fixes

 

Thanks to the providers of the following pages that I've used to help in the making of this dashboard.

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/04/18/ssisdb-reporting-with-power-bi/
Chris Schmidt

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/
KenPuls

Star Ratings Quick Measure
http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903#M12

cwebb

https://ssisreportingpack.codeplex.com/
Jamie Thomson

Lots of Dax help and great OK Vis PowerBI Visualisations
http://www.sqlbi.com/

SSIS Catalog DB
https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

Reza Rad's Article on the SSIS Catalog
http://www.rad.pasfu.com/index.php?/archives/75-SSIS-Catalog-Part-3-Folder-Hierarchy;-Folder,-Projects-and-Packages.html
DB Diagram
http://www.rad.pasfu.com/ssis/ssiscatalogpart3/5.png

SSIS Error Codes
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference

 

Enjoy

 

Phil

 

 

 

 

 

 

eyJrIjoiYmY4NTM4NWUtM2RjMS00YmVhLWI1ODAtOTM1NzZmNDgyMzVlIiwidCI6IjIxYjJlNDJkLTBkZWYtNGUxZC04OWY1LTEyZmMwZTJhZmQwNCIsImMiOjh9

---

---

stretcharm wrote:

Hi,

This is not so much a Data Story but a template for a dashboard for looking at your own SSIS activity.

I use SSIS extensively with my job and over the years have used various tools to help me manage the packages and triage issues.
These include my own SSIS logs, the out of the box SSMS reports, Jamie Thompson's report pack and a number of SSIS catalog queries.

I had a quite good single query SSIS PowerBI Dashboard, but it was slow on large catalogs and limited in its use.

I started working on new dashboard that provided all of the features from all my past tools and around the time I saw a post from Chris Schmidt (link below) and so decided to use this as a starting point.

My dashboard is still not finished, but after lots of iterations I think it's going to be useful for others who need to monitor and understand what their SSIS packages are doing.

 

I first posted this a in 2017 and I've just given it an overhaul to support some of the new feature now supported by the August 2018 Desktop & Report server versions. Please give it a try and let me know if you spot any problems or things that could be improved.

I bring in and summarise the data is at a variety of levels

Execution (Execution & Execution Summary) The Package that is Executed First which I've named RootPackageName. This includes the Project & Folder
Package (Executable Package Stats) Package Summary. I group Packages by Types e.g. Master/Dimension/Fact/Stage/PostProcess. You can customised these in the PackageTypes Table. Click Advanced Editor to change it.
Executable (Executable Stats) Items inside the Package e.g. Tasks/Data Flows/Sequence. I also call this level Package Steps
I categorise packages and task into types using a keyword lookup. e.g. Package starting Fact for loading Fact tables.

The Task type names are based on common SSIS naming conventions.

 

This dashboard also provides details of any package errors and maps them to the Microsoft IS Error Reference Names.

Individual Executions can be viewed as a text based Gantt chart or as a matrix of executions by time slices.

 

Finally I extracted the Hierarchy from the execution paths to make a Network diagram and Sankey to visualise the structure and levels in an SSIS project.

 

Whilst developing this dashboard I managed to combine and try out a number PowerBI techniques such as;

Dax Measure to display a Gantt chart (Based on Chris Webb's Star Ratings)
Turn Package Paths into a Network diagram & Sankey Diagram
Scatter Chart to show hour and day matrix
Mini Gantt Timeline with Matrix
Mini Gantt Timeline with Scatter Chart
Dax Tables for Summary data
Vlookup function to use Package names to provide a type. This can do contains matches and use Start and End Indicators.
M Language tables for reference data
Parameterised SQL to Configure the Days to Load
Standard Deviation for tolerances.

Bookmarks & Selections

Report Tooltips

I've tried to keep the custom visuals to a minimum but I have used Sankey/Network Navigator as well as the OKViz Bullet and Sparklines.
 
The PowerBI is blank with that needs the server parameter setting to your SSIS db server before applying the changes and granting permissions.

The next network and rows can take a while to load wiht a large Catalog db so I have a small version that excludes these.

Templates and sample screen shots are available here https://github.com/stretcharm/SSIS-DB-Dashboard

 

Release Notes

v1.00   

Improved query on Exectutions and Executables

Added Include fields for the Package types to configure the Master & Package Page filtering.
Edit PackageTypesUse Query to configure.

Changed Package filters

Add Date, Start, Execution ID Hierarchy to the Master and Package Pages

Added Preserve Layers Bookmark to help avoid window coming to the front

Remove Optional Job Server Code
Add Icon Ribbon and Help Page.
Minor Fixes

 

Thanks to the providers of the following pages that I've used to help in the making of this dashboard.

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/04/18/ssisdb-reporting-with-power-bi/
Chris Schmidt

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/
KenPuls

Star Ratings Quick Measure
http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903#M12

cwebb

https://ssisreportingpack.codeplex.com/
Jamie Thomson

Lots of Dax help and great OK Vis PowerBI Visualisations
http://www.sqlbi.com/

SSIS Catalog DB
https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

Reza Rad's Article on the SSIS Catalog
http://www.rad.pasfu.com/index.php?/archives/75-SSIS-Catalog-Part-3-Folder-Hierarchy;-Folder,-Projects-and-Packages.html
DB Diagram
http://www.rad.pasfu.com/ssis/ssiscatalogpart3/5.png

SSIS Error Codes
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference

 

Enjoy

 

Phil

 

 

 

 

 

 

eyJrIjoiYmY4NTM4NWUtM2RjMS00YmVhLWI1ODAtOTM1NzZmNDgyMzVlIiwidCI6IjIxYjJlNDJkLTBkZWYtNGUxZC04OWY1LTEyZmMwZTJhZmQwNCIsImMiOjh9

---

Is there any way to merge multiple SSIS servers into a single dashboard?  We have quite a few SSIS servers and having a dashboard for each seems a bit "much".