If you need to loop through and XML file, or just want to have a loop in an SSIS package loop through a list you have created using a for each nodelist is the task to use.
First we create a for each loop and set the collection to node list enumerator. Then we set the document source type to file connection and the document source to our XML file. I suggest using a file instead of direct XML because this allows us to update the package externally and prevents us from having to redeploy the package. I have a simple XML file on my C drive I have selected.
Set the enumeration type to NodeText. Set the OuterXpathStringSourceType to DirectInput and the OuterXpathString to the node we need to find. We could set this to an external file also which would allows us to change this outside the package, for this example we will set it to DiectInput. We also need to place “//” in front of the node value. Now the “for each loop” will run for each node in the XML file that matches our Outerxpathstring.
We can then set a variable to capture the node information and use it in our package.
It this example we have a list of databases that we could use to dynamically set the connections used in our data flows.