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.
