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

Find ramblings

Thursday, February 2, 2012

For Loop Container with SSIS EzAPI

In the this edition of how-to recipes for the SSIS EzAPI library, I examine the object model for the For Loop Container Prior to this post, I had never used the a For Loop Container. It took me a few minutes to figure out how to make the Expression assignment work as this is the only place it is a valid operation. The documentation even states as much To create the initialization and assignment expressions, you can use the assignment operator (=). This operator is not otherwise supported by the Integration Services expression grammar and can only be used by the initialization and assignment expression types in the For Loop container.

As noted in an earlier article, the EzAPI implements a For Loop Container wrapper class, EzForLoop, which is derived from the common EzContainer.

Recipe 03, For Loop Container

This code will create a two For Loops nested inside of each other. This is the first package in the series that actually does something. Cue the rejoicing. Through the power of For Loops and the judicious use of variables, expressions and precedence constraints, this package will count the days in a month. I selected February 2012 as it's a leap month and that's always fun.

The outer For Loop is going to initialize the StartDate variable to 2012-02-01 from it's design-time value of 2012-01-01. The loop will then test whether the StartDate is less than the EndDate. Until that condition evaluates to false, the loop will modify the value of StartDate to be the result of adding 1 day to the current value of StartDate.

There isn't much to see when that happens, you hit F5, the box goes green and it's done. Clever people can set breakpoints to see that it's performing as expected but that's work. So, there is an inner For Loop set up. This is an unusual For Loop as it is designed to only ever execute one iteration. The starting point value changes per execution of the outer loop but the terminal condition is always one greater than it. The side effect of all of this is the value of the Count variable is incremented once and only once every time the "Counter loop" is called, effectively performing a count. This can be a handy replacement for script tasks performing an equivalent task.

Once the outer loop, "For Loop demo" has completed there are two sequence containers attached to it. There is a precedence constraint on them evaluating whether @Count matches the static @ReferenceCount. If those two variables are equal, the Happy Path will light up. Otherwise, Sad Path lights up.

   1:  /// <summary>
   2:  /// Create a package with a for loop (iterates through all the days of February). 
   3:  /// Package will create plenty of variables: 
   4:  /// StartDate: datetime, 
   5:  /// StopDate: datetime, 
   6:  /// Step: int, defines increment size (currently set to 1) and interpreted as 1 day.
   7:  /// Count: int, counts the loop iterations 
   8:  /// ReferenceCount: int, static value used to verify the logic is sound. Scoped to For Loop
   9:  /// LeadingCount: int, used for the inner counting for loop to ensure 1 iteration
  10:  /// </summary>
  11:  private static void MakeForPackage()
  12:  {
  13:      string outputFile = string.Empty;
  14:      EzPackage ezPackage = null;
  15:      EzForLoop forLoop = null;
  16:      EzSequence happyPath = null;
  17:      EzSequence sadPath = null;
  18:      Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint happyPC = null;
  19:      Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint sadPC = null;
  20:      string packageName = @"EzAPI_Recipe03";
  21:      string initialExpression = string.Empty;
  22:      string evalExpression = string.Empty;
  23:      string assignExpress = string.Empty;
  24:      bool readOnly = false;
  25:      string nameSpace = "User";
  26:   
  27:      // Create 4 variables as described above
  28:      Dictionary<string, object> packageVariables = new Dictionary<string, object>()
  29:      {
  30:          {"StartDate", new DateTime(2012,01, 01)},
  31:          {"StopDate", new DateTime(2012,03, 01)},
  32:          {"Step", 1},
  33:          {"Count", 0},
  34:          {"ReferenceCount", 29},
  35:      };
  36:      outputFile = string.Format(@"{0}\{1}.dtsx", Driver.PackagePath, packageName);
  37:   
  38:      // Instantiate and configure our package
  39:      ezPackage = new EzPackage();
  40:      ezPackage.Name = packageName;
  41:      ezPackage.Description = "Recipe 03, this amazing package counts the number of days in February as a demonstration of the For Loop container";
  42:   
  43:      // Add the required variables
  44:      foreach (string variable in packageVariables.Keys)
  45:      {
  46:          ezPackage.Variables.Add(variable, readOnly, nameSpace, packageVariables[variable]);
  47:      }
  48:   
  49:      // Configure the outer loop.
  50:      // InitExpression: Initialize our StartDate variable to 2012-02-01 (design-time value is 2012-01-01)
  51:      // EvalExpresion: Test whether StartDate is less than StopDate
  52:      // AssignExpression: Increment our StartDate variable by Step variable days
  53:      forLoop = new EzForLoop(ezPackage);
  54:      forLoop.Name = "For Loop demo";
  55:      forLoop.Description = packageName;
  56:      forLoop.InitExpression = "@[User::StartDate] = (DT_DATE)\"2012-02-01\"";
  57:      forLoop.EvalExpression = "@[User::StartDate] < @[User::StopDate]";
  58:      forLoop.AssignExpression = "@[User::StartDate] =  DATEADD( \"day\", @[User::Step] ,  @[User::StartDate] )";
  59:   
  60:      // Create a variable will be used to control the inner loop's execution count
  61:      forLoop.Variables.Add("LeadingCounter", readOnly, nameSpace, 0);
  62:   
  63:      // Configure the inner loop to count the number of times it is run. Cheesy but it works
  64:      // InitExpression: Initialize @LeadingCounter to @Count plus one
  65:      // EvalExpresion: Test whether @Count is less than @LeadingCount
  66:      // AssignExpression: Increment @Count by one
  67:      EzForLoop counterLoop = new EzForLoop(forLoop);
  68:      counterLoop.Name = "Counter loop";
  69:      counterLoop.Description = "This increments the @Count variable.";
  70:      counterLoop.InitExpression = "@[User::LeadingCounter] = @[User::LeadingCounter] + 1";
  71:      counterLoop.EvalExpression = "@[User::Count] < @[User::LeadingCounter]";
  72:      counterLoop.AssignExpression = "@[User::Count] = @[User::Count] + 1";
  73:   
  74:      // Provide an easy something to apply a breakpoint to
  75:      // As I've only covered one other control flow item thus far, this places a sequence
  76:      // container inside the two for loops
  77:      EzSequence breakPointMe = new EzSequence(counterLoop);
  78:      breakPointMe.Name = "Breakpoint me";
  79:      breakPointMe.Description = "Put a breakpoint on me to confirm the for loop is acting as expcted";
  80:   
  81:      // Create two Sequence containers connected to outer loop
  82:      // Set expression on precedent constraint so non-breakpointed executions show whether @Count matched the @ReferenceCount
  83:      happyPath = new EzSequence(ezPackage);
  84:   
  85:      happyPath.Name = "Happy Path";
  86:      happyPath.Description = "Expected scenario. @[User::Count] == @[User::ReferenceCount]";
  87:   
  88:      happyPC = ezPackage.PrecedenceConstraints.Add(forLoop as EzExecutable, happyPath as EzExecutable);
  89:      happyPC.Name = "Happy Path PC";
  90:   
  91:      happyPC.EvalOp = Microsoft.SqlServer.Dts.Runtime.DTSPrecedenceEvalOp.ExpressionAndConstraint;
  92:      happyPC.Expression = "@[User::Count] == @[User::ReferenceCount]";
  93:   
  94:      // Configure the logical failure path
  95:      sadPath = new EzSequence(ezPackage);
  96:      sadPath.Name = "Sad Path";
  97:      sadPath.Description = "Egads! Who doesn't understand leap year? @[User::Count] != @[User::ReferenceCount]";
  98:   
  99:      sadPC = ezPackage.PrecedenceConstraints.Add(forLoop as EzExecutable, sadPath as EzExecutable);
 100:      sadPC.Name = "Sad Path PC";
 101:      sadPC.EvalOp = Microsoft.SqlServer.Dts.Runtime.DTSPrecedenceEvalOp.ExpressionAndConstraint;
 102:      sadPC.Expression = "@[User::Count] != @[User::ReferenceCount]";
 103:   
 104:      ezPackage.SaveToFile(outputFile);
 105:  }

Hooray, there are 29 days in February for the year 2012.

From EzAPI

Review

For Loop containers derive from the same base class as Sequence Containers. We are now fluent in the creation and configuration of For Loop and Sequence Containers via variables, expressions and pure dumb luck.

No comments: