SSIS Dependency Diagram with DiagrammeR and Sprockit

We can use DiagrammeR and Sprockit to discover SQL Server Integration Services (SSIS) dependencies. SSIS is a mature data integration tool bundled with SQL Server.

SSIS, like many other tools, can lead to “dependency hell”. This is where packages or stored procedures are developed that carry out many tasks. With many of these packages it can be very hard for a new developer to understand the dependencies. This makes for expensive maintenance, testing and error recovery.

There are frameworks around that can limit this. One of the execution frameworks I’ve had lots of success with is Sprockit.

Sprockit is a powerful but lightweight ETL process controller for managing SSIS packages and T-SQL stored procedure execution. It’s free, open source and written purely in T-SQL. Richard Swinbank developed it.

In this post I will show how you can create a diagram of dependencies with R stats, taking up from the end of the tutorial on Richard Swinbank’s site.

We first need to create a stored procedure that we can query from R. This procedure creates digraph text. I have to give a lot of credit to Richard, as he developed the initial SQL before I hacked it to work with DiagrammeR.

USE SprocketTutorial;
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE sprockit.Diagrammer
AS

DECLARE @gv NVARCHAR(MAX) = N'';

-- Nodes (processes)
SELECT @gv += N'n' + CAST(ProcessId AS VARCHAR) + N' [label=''' + ProcessName + N''']' FROM sprockit.Process;

-- Edges (process dependencies)
SET @gv += N'';

SELECT @gv += N'n' + CAST(PredecessorId AS VARCHAR) + N' -> n' + CAST(SuccessorId AS VARCHAR)
FROM sprockit.uvw_ProcessDependency;

SELECT 'digraph G {

graph [layout = dot]

node [shape = circle,
style = filled,
color = lightgrey,
fontname = Helvetica,
fixedsize = true,
width = 2.5]' + @gv + '}' AS res;

RETURN;
GO

I’ve included some format code in the procedure:

graph [layout = dot]
node [shape = circle,
      style = filled,
      color = lightgrey,
      fontname = Helvetica,
      fixedsize = true,
      width = 2.5]

This is used by DiagrammeR, and you can adjust it to your own taste. With the stored procedure done we can look at the R code. I’m using RODBC to connect to SQL Server. The database is on my machine and I am an admin on the database called SprocketTutorial. When the data gets into R it comes with \r\n, which needs removing.

require("RODBC")
require(DiagrammeR)

#open the ODBC connection
dbhandle <- odbcDriverConnect('driver={SQL Server};server=DESKTOP-31S60FC;database=SprocketTutorial;trusted_connection=true')

# construct a query string
query <- "EXEC [sprockit].[Diagrammer]";
# execute the query
df<-sqlQuery(dbhandle, query)

#Remove \R\N
clean <- gsub("\r\n"," ", df$res)

grViz(clean)

Based on the tutorial on Richard’s site, the result is a diagram of the processes and their dependencies. Have a look at the DiagrammeR site, as there is a lot more power available for creating these graph diagrams.

This diagram is great for helping developers understand the dependencies at the heart of Sprockit execution, and you can run this code as part of your CI pipeline to keep the documentation up to date.