I have some simple XML to parse within my SSIS package


and I want to take an image and copy the physical image referred to by source to destination. I knew I needed to use the foreach Nodelist enumerator, but I didn't really know where to start as the book I have on my desk doesn't seem to cover it at all.

Attempt One

I found a useful article SSIS Debugging foreach NodeList enumerators which was a start, but didn't seem to get me where I wanted to be as with XPath settings of

Enumeration type: NodeText
OuterXPathString: /

mapping to a string variable named variable (original heh, but this was a proof on cencept) with index 0 I got data in the variable of 8162_5260.JPG87775588_01.JPG8162_5260_01.JPG87775588_02.JPG. Right data, but not in a useable format.

With the XPath settings of

Enumeration type: ElementCollection
OuterXPathString: /images
InnerElementType: NodeText
InnerXPathString: *

my variable has the data 8162_5260.JPG87775588_01.JPG in it on the first, and worryingly only, iteration.

Attempt Two

Obviously doing something wrong, so I hit google again, and this time found a DrivenBySQL example, which has pictures and everything (and it contains a link to Visual XPath, which was a useful tool for validating my XPath statement).

So, I changed the XPath settings to be

OuterXPathString: /images/image
InnerElementType: Node
InnerXPathString: .

capturing the output in a variable of type Object

I then wrote some script to get the data from my XmlNode into 2 string variables, this script was heavily based upon the sample code from the DrivenBySQL example but with the follwing definitions for string and destination based on the appropriate entites, rather than the attributes in the example

strSource = objNode.SelectSingleNode("source").InnerText.ToString
strDest = objNode.SelectSingleNode("destination").InnerText.ToString

All now seems to be working. Hurrah!