My euphoria at getting my SSIS Foreach Nodelist enumerators working was short lived. I had some really strange behaviour going on with my foreach nodelist. It appeared that the XML being interpreted by the node wasn't the XML being passed in. I am using the method described in the DrivenBySQL example, using a node and putting the node contents into an object variable objNode. This objNode is then broken into its composite elements in some script (as described the other day). Those composite elements are then processed. What was happening was that the processing was doing the data for this iteration, but also every other iteration. I wanted to prove that this was really happening, and I wasn't just having a funny five minutes. So I started a new project and created some standard Direct Input XML, one for images and one for some random data.

Images XML - outer loop

<images>
  <image>
    <source>8162_5260.JPG</source>
    <destination>87775588_01.JPG</destination>
  </image>
  <image>
    <source>8162_5260_01.JPG</source>
    <destination>87775588_02.JPG</destination>
  </image>
</images>

Data XML - Inner loop

<ROOT>
  <Item>
    <Data>Entry</Data>
  </Item>
</ROOT>

I've used a string variable to put comments of what is going on in. So, it puts a started tag and a date time stamp in.
I loop through the image nodes in the outer loop - which should result in two iterations.
Within the outer loop
  I add a log entry to the string with the Source entity data in.
  I then loop through the Item nodes in the inner loop - which should result in one iteration.
  Within the inner loop
    I add a log entry with the datetime stamp and the Data entity.
  Inner loop ends
Outer loop ends

Expected Output

Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12

so with 1 For each line per iteration, but what I actually get is:

Output

Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12

which implies that the data being returned in the inner foreach contains data from both the 1st and 2nd iterations, which is what I was seeing in my problematic project.

I then changed the images XML to add a third image, and ran the test and got the following output:

Output

Started at 11/08/2006 12:36:21
Image 8162_5260.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 100_1000.JPG
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
Completed at 11/08/2006 12:36:22

again implying that the data in the inner foreach contains both the current iteration, and any previous iterations.

I took a search on the MSDN SQL Server Integration Services and found Nested Loops in the Control Flow to be the closest to my issue. Having to create a child package sounds like a workaround rather than a solution to me but I figured it was worth a try and created a child package which just has the inner for each loop in it, and got the following output:

Output

Started at 14/08/2006 15:38:47
Image 8162_5260.JPG
For each logged at 14/08/2006 15:38:48 with data of Entry
Image 8162_5260_01.JPG
For each logged at 14/08/2006 15:38:49 with data of Entry
Completed at 14/08/2006 15:38:49

So, finally it works as expected. Now to go and integrate it into the main project rather than just this test one.