In SSIS, how to loop through the XML inside a specific element using XPATH in a Foreach NodeList Enumerator

Question

I am trying to loop through the XML elements of an XML input file in SSIS. For this I am trying to use a 'Foreach Loop Container' with a Foreach Loop Container. Inside the ForEach Loop Container, I'd like to get the xml code as a String Variable.

I am fairly new to SSIS, and have been browsing the web for quite some time, yet could not get it right..

Input file example; (Note that there are some cyrillic symbols in there, which may cause issues. However, when I take out the cyrillic symbols, the same error message shows).

I am trying to loop through the nodes.

This is the package I have created so far;

Below the ForEach loop; I have a variable called 'CurrencyRate' which is a String mapped there.

And the XML Task inside the loop. This is just for testing purposes, it should write the last node element to that output file, with tags and values.

However, if I run the code as it is, I get the below error message, even though I do not see any NULL values in my data. And I have no idea where the japanese(?) symbol comes from..;

Any help would be greatly appreciated! SSIS looks like a powerful tool and I would love to be able to be able to wield it.

Answer 1

Instead of the Foreach NodeList Enumerator I am now using the Foreach From Variable Enumerator.

This is the entire package;

Using following variables;

With below script in the first Script Task 'CurrencyRates from XML to Array'; This code will get the XML and put each node as an object in an array. This array is then passed to an object variable.

        public void Main()
    {
        // TODO: Add your code here

        System.Collections.ArrayList arr = new System.Collections.ArrayList();

        XmlDocument doc = new XmlDocument();
        doc.Load((String)Dts.Variables["User::file_path"].Value + Dts.Variables["User::file_name"].Value);
        XmlElement root = doc.DocumentElement;
        XmlNodeList nodes = root.SelectNodes("//CurrencyRate"); // You can also use XPath here
        foreach (XmlNode CurrencyRate in nodes)
        {
            arr.Add((String)CurrencyRate.InnerXml);
        }


        Dts.Variables["User::CurrencyRates"].Value = arr;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

This object variable is then looped using the Foreach From Variable Enumerator, mapping the nodes to an object variable 'CurrencyRate_Item'. In the loop, this item is moved in a String variable, which we can then use for our needs.