skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Data Stories Gallery
    • Re: SSIS Catalog DB Dashboard

    Re: SSIS Catalog DB Dashboard

    10-31-2018 14:51 PM

    stretcharm
    Memorable Member
    15957 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    SSIS Catalog DB Dashboard

    ‎09-03-2017 01:21 PM

    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

    SSISDB_Dashboard_v1_03_Summary-Summary2.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Summary-ExecutionList.PNGSSISDB_Dashboard_v1_00_Execution-Gantt.PNGSSISDB_Dashboard_v1_00_Execution-ExecutionSummary.PNGSSISDB_Dashboard_v1_00_OverTime-StepsOverTime.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Network-Sankey.PNG

    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,-Projec...
    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-ref...

     

    Enjoy

     

    Phil

     

     

    Follow me on twitter https://twitter.com/StretchArm1 for updates


    Other gallery entries

    PowerBI/SQL Server and Microsoft Business Application Related YouTube Videos
    https://community.powerbi.com/t5/Data-Stories-Gallery/PowerBI-SQL-Server-and-Microsoft-Business-Appl...)

    SQL Saturday Sessions https://community.powerbi.com/t5/Data-Stories-Gallery/Pass-SQLSaturday-Dashboard/m-p/489529#M2153

    Microsoft Bus App Summit & SQL Bits videos

    https://community.powerbi.com/t5/Data-Stories-Gallery/Search-Microsoft-based-Business-Application-vi...

    and PowerBI Custom Visual

    https://community.powerbi.com/t5/Data-Stories-Gallery/PowerBI-Custom-Visuals/td-p/724185

    Most code is also here https://github.com/stretcharm

     

     

     

     

    eyJrIjoiZDM0N2NmMjMtZmE2NS00MDE1LWJkOWQtZmFmZmZkMzI4ZDBkIiwidCI6ImEwYTcyYzIzLTdlMWEtNGYxOC05NDU4LTlhNzUyYTEzMTg1NCJ9

    Preview file
    93 KB
    SSISDB_Dashboard v1.03 Blank.pbix
    Labels:
    • Labels:
    • How To
    • Other
    • Technology
    Message 1 of 59
    48,681 Views
    20
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    MednaxKevin
    MednaxKevin
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-27-2019 01:52 PM


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

    Message 58 of 59
    16,251 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to MednaxKevin
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-28-2019 03:41 AM

    @MednaxKevin wrote:


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


    I run on different environments and prefer to have different dashboards. Mainly for performances and clarity reasons.  

     

    The dashboard could be altered to combine multiple servers.

    If you could take the code from github and you cab duplicate source queries and merge the results.

     

    add some more Parameters for the other servers

    Follow this process for each query

    Duplicate Query 1 e.g Packages to PackagesServer1 to PackagesServerX

    Change the parameters to the  correct server

    on one Query use Append Queries as New to create AllPackageServers and include each of the PackagesServer queries

    Get the Code (Advanced Editor) from AllPackageServers  and paste over the original Packages Query code

    You can discard AllPackageServers  and uncheck enable load for each for the PackageServer Queries (unless you need to debug their results)

    Powerbi will now use the combined results instead of the single server version

     

    The biggest issue you'll have will be with any duplicates from the different servers. Any Number based keys such as PackageID and ExecutionID could duplicate. You may need to add a server prefix to anything that has a problem.

    I would definitely add a server code to the Project or Folder Name so that you can see the source server

     

    The other option would be to use SSIS to collate all the data into a set of tables (based on the current queries) and then update the dashboard queries to use these. You could control and manage issues like duplicate keys as you load the data. This would definitely give better performance, but you'd have to update the tables before the dashboard.

     


     

     

     

    Message 59 of 59
    16,225 Views
    0
    Reply
    hariannem93
    hariannem93
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-11-2019 10:25 AM

    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

    SSISDB_Dashboard_v1_03_Summary-Summary2.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Summary-ExecutionList.PNGSSISDB_Dashboard_v1_00_Execution-Gantt.PNGSSISDB_Dashboard_v1_00_Execution-ExecutionSummary.PNGSSISDB_Dashboard_v1_00_OverTime-StepsOverTime.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Network-Sankey.PNG

    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,-Projec...
    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-ref...

     

    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

    SSISDB_Dashboard_v1_03_Summary-Summary2.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Summary-ExecutionList.PNGSSISDB_Dashboard_v1_00_Execution-Gantt.PNGSSISDB_Dashboard_v1_00_Execution-ExecutionSummary.PNGSSISDB_Dashboard_v1_00_OverTime-StepsOverTime.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Network-Sankey.PNG

    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,-Projec...
    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-ref...

     

    Enjoy

     

    Phil

     

     

     

     

     

     

    eyJrIjoiYmY4NTM4NWUtM2RjMS00YmVhLWI1ODAtOTM1NzZmNDgyMzVlIiwidCI6IjIxYjJlNDJkLTBkZWYtNGUxZC04OWY1LTEyZmMwZTJhZmQwNCIsImMiOjh9

     

    Message 57 of 59
    16,481 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-08-2018 11:36 AM

    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
    SSISDB_Dashboard_v1_00_Summary-Summary2.PNG
    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.

     

    SSISDB_Dashboard_v1_00_Summary-ExecutionList.PNGSSISDB_Dashboard_v1_00_Execution-Gantt.PNGSSISDB_Dashboard_v1_00_Execution-ExecutionSummary.PNGSSISDB_Dashboard_v1_00_OverTime-StepsOverTime.PNG

    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.

     

    SSISDB_Dashboard_v1_00_Network-Sankey.PNG

    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,-Projec...
    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-ref...

     

    Enjoy

     

    Phil

    SSISDB_Dashboard v1.00 Blank.pbix
    Message 44 of 59
    16,998 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-12-2018 03:34 PM

    Is there a trick to getting the dataset to refresh on a schedule when the report is published to PowerBI?

     

    I get an error that the dataset has one or more sources that don't support refresh. Clicking discover data sources gives me the response "Query contains unknown or unsupported data sources".

     

    I love the report though. I jsut wantto be able to share it with the entire team in main site for viewing diagnostics on our various servers.

     

    Thanks,

    Phil

    Message 45 of 59
    16,067 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-13-2018 05:15 AM

    In order to get the powerbi service working you'll need to use the Gateway to connect to your servers (unless they are in azure )

    https://powerbi.microsoft.com/en-us/gateway/

    You'll need pro licences to share and use the gateway.

     

    I share the report via the team on premise using the PowerbI Report Server (SSRS + PowerBI) can be added to existing sql servers you have enterprise and maintainance. Just need a powerbi pro licence to upload reports.

    https://powerbi.microsoft.com/en-us/report-server/

     

    Both of these would then let you set a schedule.

    Message 46 of 59
    16,062 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-13-2018 05:27 AM

    I do have enterprise gateways and I just updated them to the September 2018 release.

    1. Is it possible not all the data comes from SSISDB?
      • Usually the error tells me more details about what database it doesn't have access to.
    2. Did you publish it to the APP workspace or do you save it in OneDrive for Business?

     I also tried to update the parameters and I got the error Failed to update paramters values. The technical details are a bunch of guids and a status code of 404.

    Message 47 of 59
    16,060 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-13-2018 06:40 AM

    Some data comes from the msdb for the SQL jobs

     

    If you don't need SQL Job replace the query "Currently Running SQL Jobs" with a dummy record set.

    I've been having some problems with this query since I changed the Server param and tried to handly it as an optional data set.

     

    let
        BlankTable = #table(
     type table
        [
            #"JobName"=text,
            #"StartExecutionDate"=date,
            #"RuNRequestedDate"=date
        ], 
    {{"Job data not available","",""}})
    in
        BlankTable

    If you do have access to the server then you can use this query

    let
    
    SQLCmd="
    
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    SELECT sj.name as JobName, sja.start_execution_date StartExecutionDate 
    
    , sja.run_requested_date RuNRequestedDate
    FROM msdb.dbo.sysjobs sj
    JOIN msdb.dbo.sysjobactivity sja
    ON sj.job_id = sja.job_id
    WHERE 1=1
    AND session_id = (
        SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity WHERE    sj.job_id = sja.job_id )
    AND sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL
    
    ",
        
        SQLJobs = Sql.Database(JobServerName, "msdb", [Query=SQLCmd]) 
    in
        SQLJobs

    I don't use the PowerBI service as we have no gateway to connect to our servers.

    Message 48 of 59
    16,055 Views
    0
    Reply
    aurelien
    aurelien
    Frequent Visitor
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-31-2018 07:31 AM

    Hello, first of all many thanks for this great job.

    I was looking forward to using it but I'm facing an issue regarding executionID.

    I have in the package table of the catalog many version_build and project_id for the same Package name.  Therefore the query "Execution" will return duplicates ExecutionID and this will prevent the other queries to excute properly, leaving the dashboard empty.

     

    Column 'ExecutionID' in Table 'Execution' contains a duplicate value '41997' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

     

    Then  on other tables I have: Load was cancelled by an error in loading a previous table.

     

    Any way to improve the data model to support multiple version / project for a package?

     

     

    Message 49 of 59
    15,972 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to aurelien
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-31-2018 02:51 PM

    I've not seen this problem in my environment.

     

    I supsect its the join to get the project id from the execution.

     

    Can you try chaning the FROM in the SQLCMD part of the Execution query. Use advanced editor to update.

     

    These joins may work, can you check that project_lsn is the same as the object_version_lsn in your environment.

     

    FROM    [catalog].[executions] ex
            JOIN catalog.projects pr ON ex.project_lsn = pr.object_version_lsn
                                        AND ex.[project_name] = pr.name
            JOIN catalog.folders f ON pr.folder_id = f.folder_id	

     

    If this doesn't work this should ensure there is only a single matching package/project combo

    FROM    [catalog].[executions] ex
            OUTER APPLY (Select Top 1 pk.project_id FROM [catalog].[packages] pk 
    		WHERE pk.name = ex.package_name
    		ORDER BY Package_id DESC) p 
            JOIN catalog.projects pr ON p.project_id = pr.project_id
                                        AND ex.[project_name] = pr.name
            JOIN catalog.folders f ON pr.folder_id = f.folder_id	

    Let me know if this works and I'll update the code in the next version.

     

     

     

    Message 50 of 59
    15,957 Views
    1
    Reply
    aurelien
    aurelien
    Frequent Visitor
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-05-2018 02:52 AM

    Hello.

     

    The first script did not do the trick as it was filtering too much data.

    So i used the second one. 

     

    Sames issue encountered on Executable query. Here is the fix I applied:

    catalog.executables AS exb
    			OUTER APPLY (Select Top 1 pk.project_id FROM [catalog].[packages] pk 
    				WHERE pk.name = exb.package_name
    				ORDER BY Package_id DESC) p 
                INNER JOIN catalog.packages AS pk ON pk.name = exb.package_name and p.project_id = pk.project_id
                INNER JOIN [catalog].executions ex ON ex.execution_id = exb.execution_id
                INNER JOIN catalog.projects pr ON pr.name = ex.project_name
                                                  AND pr.project_id = pk.project_id

     

    It's all working now. Thanks!

    Message 51 of 59
    15,896 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to aurelien
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-05-2018 12:18 PM

    Great stuff glad it worked. I'll update the next version.

    Message 52 of 59
    15,884 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-16-2018 05:06 AM

    Really good stuff - this project saved me a TON of time.  Hopefully I can contribute for future releases.

     

    I also got a duplicate error on the executions table - here was my fix based on this documentation of that table

    https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-dat...

     

    FROM [catalog].[executions] ex
    JOIN [catalog].[packages] p ON p.name = ex.package_name and case when ex.object_type = 20 then p.project_id else p.package_id end = ex.[object_id]
    JOIN catalog.projects pr ON p.project_id = pr.project_id
    AND ex.[project_name] = pr.name
    JOIN catalog.folders f ON pr.folder_id = f.folder_id

     

    The issue was caused by multiple users deploying the project - that created a different project_ID for that user for the same project - really bad design microsoft.... use an "updated by" field for a consistent project id - versus creating a new project ID record.

    Message 53 of 59
    15,786 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-16-2018 05:30 AM

    And also had to change the Executables table to this (the other change was on the Executions table itself) - I imagine this would be impossible to troubleshoot if you didn't have multiple people deploying the same project into SSISDB.  This is on SQL Server 2014 - project only deployment ... maybe fixed in 2016.

     

    FYI - I think the top 1 on outer apply will filter out history - as the execution history is tied to this new project ID / package ID record being created.

     

      FROM     catalog.executables AS exb

                INNER JOIN [catalog].executions ex ON ex.execution_id = exb.execution_id

                INNER JOIN catalog.packages AS pk ON pk.name = ex.package_name and case when ex.object_type = 20 then pk.project_id else pk.package_id end = ex.[object_id]

                INNER JOIN catalog.projects pr ON pr.name = ex.project_name

                                                  AND pr.project_id = pk.project_id

    Message 54 of 59
    15,784 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-20-2018 11:02 AM

    Thanks for this.

     

    I'm still getting some duplicates as I have multiple projects that have the same package. I'm working on an update which will hopefully minimise the issue. If not I may need to change the model to support these details.

    Message 55 of 59
    16,769 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to stretcharm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-06-2019 01:11 PM

    V1.03

    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

     

    SSISDB_Dashboard_v1_03_Summary-Summary2.PNG

     

    SSISDB_Dashboard v1.03 Blank.pbix
    Message 56 of 59
    16,722 Views
    0
    Reply
    jdrazkowski
    jdrazkowski
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-19-2018 05:29 AM

    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?

    Message 39 of 59
    17,153 Views
    0
    Reply
    dooncomputer
    dooncomputer
    Frequent Visitor
    In response to jdrazkowski
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-31-2018 12:45 PM

    I would be nice to have option to enter diffferent server name for MSDB and SSISDB?

    Message 42 of 59
    17,034 Views
    0
    Reply
    stretcharm
    stretcharm Memorable Member
    Memorable Member
    In response to dooncomputer
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-01-2018 02:30 AM

    The database and server are both parameters so you can set them to anything, however they are currently the same server.

    You can adjust your version to add yourself a new server param for your msdb query in the query editor.

    I'll add the MSDB and Job Server as new params in the next version. I'll also add an option to disable this as I don't always have access to the job details.

     

     

    SSISDB_Dashboard Params.PNG

    Message 43 of 59
    17,025 Views
    0
    Reply
    dooncomputer
    dooncomputer
    Frequent Visitor
    In response to jdrazkowski
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-31-2018 12:36 PM

    I would be nice to have option to enter diffferent server name for MSDB and SSISDB?

    Message 41 of 59
    17,034 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices