09-03-2017 13:21 PM - last edited 08-29-2019 14:23 PM
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
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
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.
Thanks to the providers of the following pages that I've used to help in the making of this dashboard.
Star Ratings Quick Measure
Lots of Dax help and great OK Vis PowerBI Visualisations
SSIS Catalog DB
Reza Rad's Article on the SSIS Catalog
SSIS Error Codes
Follow me on twitter https://twitter.com/StretchArm1 for updates
Other gallery entries
PowerBI/SQL Server and Microsoft Business Application Related YouTube Videos
Microsoft Bus App Summit & SQL Bits videos
and PowerBI Custom Visual
Most code is also here https://github.com/stretcharm
Updated version 0.77 templates (blank docs as I cannot upload templates)
Changed to use Ribbon charts
Fixed Gantt Calculation
Added Some Drillthroughs
Fixed Date join issue
Improved query performance on large SSIS DBs
Small template doesn't include the rows or network diagrams that can be slow.
I've also added a post in the Quick Measures for the Text Gantt Dax
Updated version 0.78 templates (blank docs as I cannot upload templates).
Use the Server Name Parameter and Apply. Then do a refresh. If you want the Rows manually Refresh Execution Stats Rows as it's not included in the refresh.
Added Percentage completion estimates to the Currently Running Tab
Improved query performance on large SSIS DBs
Execution Stats has commented out the time filter as its quicker on my server. If you have a large ssisdb you may want to uncomment.
Improved query for Execution Hierarchy and it's now included in the Refereses
Improved query for Execution Stats Rows. Still not not included in the refresh as it can be slow, but you can refresh manually.
Add a Execution Comparison tab
Add a Execution Over Time tab using scatter play axis
Here are some new screen shots
This is amazing!
We have an SSIS environment that runs all packages from file system and we do not have an SSISDB. Is this still able to work? I was unable to get it set up because of the missing SSISDB. Packages are all on file system.
Thanks for contributing this!
I'm afraid no it only works with SSISDB,
I recommend if your using SQL 2012 or above you try deploying to the catalog as you get all the SSISDB logging for free and it's very good.
In theory you could use file based ssis and create similar logs your self, but it would be lots of work. If they logs are the same as the SSISDB tables you could adapt the powerbi to use files instead of a db.
Is there a reason why your not using the SSIS Catalog?
OK. You could suggest they get with the times.
If they give you any execution logs you could try building your own dashboard from them.
PowerBI is also very good at loading data from files.
Most of my dashboard is driven from the execution statistic data.
I sent the inquiry today as to whether this is something on their roadmap. I will have to look in to exection logs. The vendor does have their own dashboard for tracking, configuring, and managing the product from an ETL/DW perspective, but they don't have any analytics built in to show trend, day over day, etc. Theorhetically, I could learn their DB well enough to build off of.