A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, February 1, 2012

Sequence Containers and precedence constraints with SSIS EzAPI

In the this edition of how-to recipes for the SSIS EzAPI library, I examine the object model for Control Flow objects and specifically examine the Sequence Container and associated precedence constraints.

EzAPI Executable model

Below is the class diagram showing the classes derived from EzExecutable. For now, we'll focus on the left substree.
From EzAPI


EzExecutable is the parent class for everything that exists on the Control Flow canvas. Derived from it are either containers (EzContainer) or tasks (EzTask). If you've futzed about with the straight SSIS object model or spent 30 seconds looking at the diagram, it should be apparent what's a container and what's a task. The observant ones among you may be wondering where the rest of the tasks are. Not currently implemented.


EzContainer is the parent for anything that implements IDTSSequence. That means if you have a third party container, you could create a subclass of EzContainer and voila, the EzThirdPartyContainer is usable from EzAPI.


EzSequence provides an interface to a Sequence Container. If you aren't using sequence containers, you're missing out on an excellent mechanism for subsetting related processing.


EzForLoop is the EzAPI's wrapper for the For Loop Container which is easily my least used container with SSIS packages.


EzForEachLoop represents the friendly implementation of a Foreach Loop Container. It's only in the past few months that I've come to realize how much power this container has. In particular, the capabilities of the "Foreach ADO.NET Schema Rowset" enumerator type blows my mind.


EzPackage is of course the Package container which everyone knows about and ... wait, what? I'll quote the code itself This is a base package class to use when dynamically constructing packages. Yes, I'm actually working the long way through EzAPI in this series. The SSIS team offers excellent out of the box, basically zero-configuration options with EzAPI but their documentation already covers them. Plus, I still had all these questions about why am I doing these things? How does the magic work? EzPackage we have already been working with. It's analogous to a Package.

Even though an EzPackage can decode a package built with BIDS and add things to it, the "extra" stuff BIDS adds into it (layout data) makes for display incompatibilities.

Recipe 02, sequence containers and precedence constraints

Lots of code here but it's not Civil war surgery.
   1:  /// <summary>
   2:  /// Recipe 2, covering sequence containers and precendence constraints.
   3:  /// </summary>
   4:  public static void MakeSequencesAndPrecedenceConstraints()
   5:  {
   6:      string outputFile = string.Empty;
   7:      EzPackage ezPackage = null;
   8:      string packageName = @"EzAPI_Recipe02";
  10:      outputFile = string.Format(@"{0}\{1}.dtsx", Driver.PackagePath, packageName);
  11:      ezPackage = new EzPackage();
  12:      ezPackage.Name = packageName;
  13:      ezPackage.Description = "Recipe 02";
  15:      // Create nested sequence containers 3 deep.
  17:      EzSequence outerSequence = null;
  18:      EzSequence middleSequence = null;
  19:      EzSequence innerSequence = null;
  21:      // The constructor for most anything in the EzAPI takes a parent object
  22:      // as an argument. Here our parent is the package itself.
  23:      outerSequence = new EzSequence(ezPackage);
  24:      outerSequence.Name = "Outer Sequence";
  26:      // By providing the outerSequence to the constructor, this will be correctly
  27:      // placed inside of the supplied container when the package executes.
  28:      middleSequence = new EzSequence(outerSequence);
  29:      middleSequence.Name = "Middle Sequence";
  30:      middleSequence.Description = "The one in the middle is the green kangaroo";
  32:      innerSequence = new EzSequence(middleSequence);
  33:      innerSequence.Name = "Inner Sequence";
  34:      innerSequence.Description = "It's cozy in here";
  36:      // add 4 more containers
  37:      for (int i = 0; i < 4; i++)
  38:      {
  39:          new EzSequence(ezPackage).Name = string.Format("We are anonymous {0}", i);
  40:      }
  42:      // at this point, I have 5 containers on the canvas and I'd like for them to be
  43:      // wired in series. Order is irrelevant. There are rules on precedence constraints...
  44:      for (int execOrdinal = 0; execOrdinal < ezPackage.EzExecs.Count -1; execOrdinal++)
  45:      {
  46:          // this simply links everything to the subsequence container
  47:          // This code will explode if you've added an executable that won't cast to EzSequence
  48:          Console.WriteLine("\"{0}\" executes after \"{1}\"", ((ezPackage.EzExecs[execOrdinal]) as EzSequence).Name, ((ezPackage.EzExecs[execOrdinal + 1] as EzSequence).Name));
  50:          // AttachTo is counter intuitive to me.
  51:          // objA.AttachTo(objB) results in a objB->objA
  52:          // ezPackage.EzExecs[execOrdinal].AttachTo(ezPackage.EzExecs[execOrdinal +1]);
  53:          ezPackage.PrecedenceConstraints.Add(ezPackage.EzExecs[execOrdinal], ezPackage.EzExecs[execOrdinal + 1]);
  54:      }
  56:      // Configure the precedence constraint
  57:      for (int i = 0; i < ezPackage.PrecedenceConstraints.Count; i++)
  58:      {
  59:          switch (i)
  60:          {
  61:              case 0:
  62:                  // Make this on error
  63:                  ezPackage.PrecedenceConstraints[i].Name = "Failure";
  64:                  ezPackage.PrecedenceConstraints[i].Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure;
  65:                  break;
  66:              case 1:
  67:                  // this always runs
  68:                  ezPackage.PrecedenceConstraints[i].Name = "Completion";
  69:                  ezPackage.PrecedenceConstraints[i].Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Completion;
  70:                  break;
  71:              case 2:
  72:                  // this runs if the preceding task succeeded AND the expression is true
  73:                  ezPackage.PrecedenceConstraints[i].EvalOp = Microsoft.SqlServer.Dts.Runtime.DTSPrecedenceEvalOp.ExpressionAndConstraint;
  74:                  ezPackage.PrecedenceConstraints[i].Expression = @"Year(@[System::StartTime]) == 2012";
  75:                  break;
  76:              case 3:
  77:                  // expression only
  78:                  ezPackage.PrecedenceConstraints[i].EvalOp = Microsoft.SqlServer.Dts.Runtime.DTSPrecedenceEvalOp.Expression;
  79:                  ezPackage.PrecedenceConstraints[i].Expression = @"Year(@[System::StartTime]) == 2012";
  80:                  break;
  81:              default:
  82:                  break;
  83:          }
  84:      }
  86:      Console.WriteLine(">>> By your command (press enter to continue)");
  87:      Console.ReadKey();
  88:      ezPackage.SaveToFile(outputFile);
  89:  }

Sequence Containers

Lines 17-34 builds a nested set of SSIS Sequence Containers: Outer, Middle and Inner. As you can see, it's simply a matter of instantiating EzSequence with the appropriate parent. Rather than accept default names (GUID), I strongly suggest you assign a useful name to everything. Lines 37 to 40 add 4 Sequence Containers that are not related to anything else. If you saved it out, it'd look something like this

From EzAPI


That's handy but what if we didn't want everything executing in parallel? How can we thread them together? There are at least two options I found in the object model: executableObject.AttachTo and executableObject.PrecedenceConstraints.Add. Be aware that they add them in different orders. Enumerating through all the objects at the control flow level (lines 44-54) calling the AttachTo method results in executables linked in backwards order to me. objectA.AttachTo(objectB) results in objectA's execution dependent on objectB. AttachTo while technically correct, seems ambiguous. You'sMyDaddyNow would be a more accurate method name, if only it was allowed by .NET... Comment out line 53 and uncommnent 52 to generate this screen shot.

From EzAPI


Running the method as is uses the PrecedenceConstraints.Add method which has better intellisense and the parameters are named execFrom and execTo. I believe this to be a far more intuitive mechanism for getting expected results as demonstrated below.

From EzAPI

Precedence Constraints

Everything that is subclassed from EzContainer has a PrecedenceConstraints collection. The base package PrecedenceConstraints collection has 4 constraints. The remaining the sequence containers, nested and un-nested have precedence constraint collections with zero elements. There must be a more graceful way of working with precedence constraints than what I'm doing here because it is kludgey as hell. Had I kept a reference to my various containers, I could have linked particular objects together instead of simply saying element N is linked to element N+1. I could not find any way to identify the constraint that was just created or to supply a name as it's created. So, even though I can access the PrecedenceConstraint collection by name, ID or index, I don't know what any of those are for the just made constraints. Using the PrecedenceConstraints.Add method returns an object of type Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint. executableObject.AttachTo returns void. Therefore, if you need to modify the precedent constraint, CAPTURE IT!

Anything you can do in the GUI, it seems you can do with the PrecedentConstraint object. In the above code, lines 57-84, I cycle through the available constraints and alter them based on their ordinal position. For the first constraint, I switch it to only running when the preceding task fails. This is accomplished by assigning the Value property of the constraint to an enumerated value. This is another one of those non-intuitive properties. The second constraint it changed to always run, regardless of execution result of preceding task.

The next two constraints are configured based on the evaluation of an expression. This is accomplished by assigning a value to the Expression property and setting the correct enumerated value to the constraint's EvalOp property. The same caveats about escaping expressions apply here.

From EzAPI


We're finally getting somewhere in the series! We can programmatically create new SSIS packages with variables, expressions, sequence container and configure precedence constraints between executables.

No comments: