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
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
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
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
EzForLoop is the EzAPI's wrapper for the For Loop Container which is easily my least used container with SSIS packages.EzForEachLoop
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
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";
9:
10: outputFile = string.Format(@"{0}\{1}.dtsx", Driver.PackagePath, packageName);
11: ezPackage = new EzPackage();
12: ezPackage.Name = packageName;
13: ezPackage.Description = "Recipe 02";
14:
15: // Create nested sequence containers 3 deep.
16:
17: EzSequence outerSequence = null;
18: EzSequence middleSequence = null;
19: EzSequence innerSequence = null;
20:
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";
25:
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";
31:
32: innerSequence = new EzSequence(middleSequence);
33: innerSequence.Name = "Inner Sequence";
34: innerSequence.Description = "It's cozy in here";
35:
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: }
41:
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));
49:
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: }
55:
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: }
85:
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 |
AttachTo
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 |
PrecedenceConstraints.Add
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 |
Review
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:
Post a Comment