BartekR profile pic


SQL Server. SSIS. PowerShell. Azure.
1 wife. 1 daughter.3 dogs. 9 cats.

Starting to learn something new - first step. Let’s analyse the code of SSIS package. How does it store the information about the element connections? How can I get that data as graph’s edges and nodes? Step by step - building the packages from empty one to more complex I will find how they are stored.

To achieve this I will prepare the new SSIS Project and call it SSIS_Graph. It will get new packages each time I will want to check something new. For start I create an empty package, then package with one Control Flow element - I will use empty Data Flow Task and the Sequence Container, then I will create two empty Data Flow Tasks and connect them with one precedence constraint (success). And so on - more precedence constraints, constraints with expressions. I won’t paste the code for all the sample packages. It’s just to help me get more familiar with DTSX XML and find the patterns. At the beginning, I will concentrate on Control Flow elements. The main goal is to visualise packages dependencies within a project and then other - dependencies between projects or dependencies between package’s elements.

When you take a look at the code of two empty Data Flow Tasks connected with precedence constraint you find those elements of interest:

  • DTS:Executables - they contain - well - Executables
  • DTS:Executable - the task on the Control Flow area
  • DTS:PrecedenceConstraints - guess?
  • DTS:PrecedenceConstraint - you guessed right!

Each element has plenty of attributes, but we mostly care about:

  • DTS:ObjectName - exactly what it says on the tin
  • DTS:refId - the path to the element (task)
  • DTS:From (for precedence constraints - starting point)
  • DTS:To (for precedence constraints - finishing point)
  • DTS:LogicalAnd (for precedence constraints - appears when it’s AND constraint; does not appear for OR constraints)
  • DTS:value (for precedence constraints - appears for constraints other than Success)
  • DTS:Expresion (when precedence constraint uses expression)
  • DTS:EvalOp (Evaluation Operation? - used when precedence constraint uses an expression)

For the graph data, I will take more of the attributes. For now, I just write down the most important of them. It’s enough to get me started. The next step: create graph tables.

Recent Posts



Posts about SQL Server, SSIS and automation for my future self, but you might find something useful.