Share via

SSIS: Debugging foreach NodeList enumerators

  • Article

Earlier today Fred S. asked a great question.

"Using the following data I’m trying to get each node Element populated into their own variables.
XML:
<DataBases>
   <DataBase>
      <Name>Sales</Name>
      <Action>DBCC</Action>
      <FLOP>1</FLOP>
   </DataBase>
   <DataBase>
      <Name>Inventory</Name>
      <Action>DBCC</Action>
      <FLOP>1</FLOP>
   </DataBase>
</DataBases>

Want to do:
Name   -> vsDataBase    
Action -> vsAction      
FLOP   -> vnFLOP
                      
Where vsDataBase, vsAction and vnFlop are variables of type string, string, int32. "

He was using For each loop's ForEach NodeList enumerator, lets see how we can do this.

There might be other ways to do it, I wanted to do it using two steps... first get a database, then gets its element values.

The result of the first step would be the nodelist, so the EnumerationType should be ElementCollection. Then there were the Outer* and Inner* properties that I confess I didn't understand.

Here's how this enumerator currently works:

- The OuterXPathString will be used to get the list of elements collection from the XML document specified earlier. In this case, our XPath string is '/DataBases/*' which would return all DataBase elements.

- For each element in the collection we now have (i.e. for each DataBase element), the InnerXPath is applied. We can set this property to be '*' (or the equivalent 'child::*') and InnerElementType of 'NodeText' so that we get the value of the child elements. Since there're multiple children per DataBase, the result of the this XPath will be an array of strings (each string is the value of the element).

For the first time through the loop, then, the current value of the enumerator is an array of three strings: {"Sales", "DBCC", "1"}. The next time through the loop the current value will be an array of three strings again: {"Inventory", "DBCC", "1"}.

Per the discussion above the current value of the enumerator during an enumeration should be an array of strings, but I wanted to see the array to make sure that was right. Turns out I had to use an undocumented feature to do this. (As soon as I post this message I'll be sending a note to the doc folks to make sure this isn't undocumented for too long because it's really useful!).

If you map the index '-1' to a variable of type 'Object', that variable gets populated with the complete collection (which is current value of the enumerator). Then I used a script task in the loop with simple code like so:

        Imports System.Collections
. . .

        Dim o As IEnumerator
        Dim o2 As Object
        o = CType(Dts.Variables("Variable").Value, IEnumerator)
        o.Reset()
        o.MoveNext()
        o2 = o.Current
 
and put a breakpoint at the last line.

When you execute the package now, the breakpoint is hit and the first time through you can see that o2 is the string "Sales". Just step through the iterator to confirm the array contents are what you expect them to be.

Knowing the contents of the array, then, it's fairly straight forward to do the mapping. Create the variables, set their types to string, then use the indices like this:

User::vsDataBase    0
User::vsAction      1
User::vsFLOP        2

Note that vsFLOP is not vnFLOP... the text is string, so you'll have to convert it to a number.

Also note that this example assumes a well formed document. Specifically that each DataBase element will have three children in the right order.

Think that's about it.

Is there a better way to do this in a loop? Let me know!

Thanks Fred for a great question.

ash

Comments

  • Anonymous
    July 29, 2006
    Today I was using the NodeList ForeachContainer and&amp;nbsp;I wanted to extract 2 values of a node and store...
  • Anonymous
    September 20, 2006
    Thank you so much for this post.  I was having a terrible time getting this stuff figured out.  Posts such as these should be included in BOL in an addendum!
  • Anonymous
    September 30, 2010
    good article  and Thank you ,   and can you please give me example(s) on Foreach Item Enumerator,Foreach ADO Enumerator,Foreach From Variable Enumerator in SSISThanks
  • Anonymous
    April 23, 2012
    Very useful post among tons of stuff :)