Using PowerShell to Control Visio

Note: Please be kind on my PowerShell programming techniques. If I am doing something really horrible, let me know. I will change my code.

As our team struggles to get to a place where we can do continuous integration, some of the biggest issues we have needed to work on is figuring out how to build out our various jobs to run maintenance tasks and SSIS packages. While building a system in PowerShell to take a set of JSON files and outputting a set of jobs and dependencies, I really wanted to find a way to visualize what turns out to be a directed graph structure. My goal is to work through that entire process in blogs, taking the job generation system I built for our corporate use and improve it, and publish the code. My goal is to fix where I made a lot of really mediocre choices in places due to not having a clue what I was doing, and still not exactly being all that great at PowerShell. The basic concepts of what to do to create an agent job (or in my case, 30 or so, agent jobs, and their dependencies on each other,) is not terribly difficult, but it is definitely different working in PowerShell, as there is not a lot of documentation out there (and seemingly less so for the Visio parts!)

For this first step, I am going to use 3 JSON files. With the structure represented in these snippets:

JOBS_Definition.Json

JOBS_Dependency.Json

JOBS_Schedules.Json

The gist of the setup is: Jobs are defined in the definition file. Initially they can be T-SQL, SSIS type jobs. System and Subsystem reference SSIS constructs of Folder and Package (and I define Project to be equal to Folder, which I may change later. But for simplicity’s sake, we keep them the same. There is a database used to hold the schedule hierarchy later, and it will be used to query to see what jobs to kick off later in the path. The files are incomplete at this point, but I will grow them to cover all of the things I am adding to the tool.

The files for this code are located in github here: https://github.com/drsqlgithub/SSISDeployTool/tree/3437402cc1e79db79ccceb83af05d9607ec864bb

All of this is going to be really hard to put into a blog, but when I am done, the entire code will be in a github repository, committed blog by blog (plus others, naturally, as I work through each step and mess things up over and over like a good programmer!), so you can go back and get the code where I was in the process.

The following is the code used. The basics are:

  1. You need to have Visio installed.
  2. The code instantiates Visio, then draws a box for every node, and a line for every Dependency
  3. Schedules are used for discovering jobs that are “scheduled” to highlighted as root nodes, and not “triggered”

The code is commented so I won’t go over it in any detail, but will answer any questions (and alter the code in the future if I learn better habits.

This code is in the LIBRARY_Functions.ps1 file, the file where all of the functions needed to implement this process will be found. The final process includes a way to copy the files from the code store, to make a “point in time” “executable” piece of code for deployment.

There are two other files involved in the Visio generation process, one called LIBRARY_Locations. This file currently only contains:

But it may later include compilers, etc. I made it code instead of Json to allow the use of variables (and in some cases, an environment, like if a common path is different on my home and work computers, or your computer. It is for code that is used on all of the programs in this library.

Finally, the file TOOL_DiagramHierarchy.ps1 includes the functions and locations, then sets the files for the process. Defines a variable to decide how chatty the application is, and then calls the SSIS_DrawHierarchyInVisio function:

Just run this file, using the supplied Json files, and you will (hopefully) see something like this:

With only 5 boxes on the diagram, it already chose to have a line overlapping an entity. No formatting type I could find in Visio or the UI did a decent job. On my diagram with 30 items, it was a bit of work to reorganize the items. It was not however, as hard as making the diagram by hand.

The reason I started with this technique was because it is a lot more exciting than many of the other tasks in the job system, and is universally valuable if you have a graph to diagram. Instead of Json, you can easily use tables (or a graph structure) and extrapolate how to do this (Well, assuming you are better at PowerShell than I am so far!)