Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts
[2017-Aug-08] Support for XML structured files as a data source has been available in Excel, then was introduced into the Power Query data extract engine of the same office application which later migrated into the Power BI data modeling tool.

My recent experience working with XML data files at the last Toronto Open Data user group meeting - https://www.meetup.com/opentoronto/events/240911274/ challenged me to look at XML files at a different angle. 

A request was made to analyze Toronto Lobbyist Registry Open Data Set with a very common scenario for an XML data file to hold parent (or master) node elements along with a subset of children (or nested nodes).

Here is an example of this Toronto Lobbyist Registry open XML data file:


And here is how this file looked in the Power BI Query Editor:


Basically, the ROW parent node had a following list of elements:
SMNumber
- Status
- Type
- SubjectMatter
- Particulars
- InitialApprovalDate
- EffectiveDate
- ProposedStartDate
- ProposedEndDate

and several sub-nodes which are shown in Power BI query editor as tables:
- Registrant
- Communications
- etc.

My challenge was to build a data model that would enable these main dataset elements analysis along with its subset data elements. It wasn't that intuitive but eventually proved that Power BI is a very decent data modeling environment.

So, first, I made my parent node as a base table with SMNumber as its Primary Key (PK). 
Then for each of the sub-node datasets: 
1) I had to create a replica of the base table; let's take Communications table for example. 
2) Then I removed every column except for the actual column with the Communications table data and I also left the SMNumber, which now had become a Foreign Key (FK) to my base table PK.


3) Then expanded Communications table columns and gave a proper name to a new table within my data model, which now showed all the evidence of a potential One-to-Many relationship between the base and this new table:


After repeating steps 1-2-3 for the other sub-nodes on my XML sourced main table I was able then to create a stable data model to accommodate my future reporting visualizations and data discovery:


One single XML file with parent and children nodes had been transformed into multiple tables, where Power BI took care of empty sub-node elements (please see the null value from the 2nd step of my child node transformation). In addition to that, a referential integrity between tables was in place and I could start working on my visualizations.


I understand that XML data source for Power BI is just a very small member of the family of other supported data sources, and they keep adding new more complicated data resources to stream your data analytics from. However, it's just another proof of how many blades this Power BI Swiss knife can have :)

Happy data adventures!
[2017-July-24] Map visualization in Power BI has drawn many data professionals into a field of сreating visual data stories with maps. It only takes a dataset of Geo located data points to do this and everything else is managed by map visual controls plus one's imagination. You have a choice to use native maps in Power BI, shape maps, filled maps, flow maps, etc. Here is a link to a recent David Eldersveld article that can give a glimpse into those maps and what you can do with them, 10 Ways to Create Maps in Microsoft Power BI.

However, there is no built-in functionality yet in Power BI that would calculate travel distance and time between Geo points. Let's say, a business owner wants to know how many customers he could deliver his product to within 5 minutes, 10 minutes, time intervals could go on.

In order to solve this problem, we can use Bing Map API resource to geocode addresses, calculate routes, get information about traffic incidents and issues in a specified area and many more things: Bing Maps REST Services.

I was inspired by Dustin Ryan's article (https://sqldusty.com/2016/04/26/power-bi-and-the-bing-maps-api/) where he explored a Geo location API using textual address information. And I went a bit further and used http://dev.virtualearth.net/REST/V1/Routes/Driving? API to calculate a route between two locations, fortunately, it can provide me with a travel time as well.

Here is an XML output from a simple web service request to calculate a route between Boston International Airport (1 Harborside Dr, Boston, MA 02128, USA) and MIT (77 Massachusetts Ave, Cambridge, MA 02139, USA) 



Which then I can replicate as a web based data source in my Power BI data model to return Travel Distance and Duration data elements:



In addition to this, I can create a function in Power BI with 2 parameters, Start and Destinations address and then use it along with my data set:



let 
Route = (StartAddress as text, DestinationAddres as text) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0="&StartAddress&"&wp.1="&DestinationAddres &"&avoid=minimizeTolls&key=BingMapsKey")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
ResourceSets = #"Changed Type"{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Changed Type1"{0}[Resources],
Route = Resources{0}[Route],
#"Changed Type2" = Table.TransformColumnTypes(Route,{{"Id", type text}, {"DistanceUnit", type text}, {"DurationUnit", type text}, {"TravelDistance", type number}, {"TravelDuration", Int64.Type}, {"TravelDurationTraffic", Int64.Type}, {"TrafficDataUsed", type text}, {"TrafficCongestion", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Id", "BoundingBox", "DistanceUnit", "DurationUnit", "TrafficDataUsed", "TrafficCongestion", "RouteLeg"})
in
#"Removed Columns"
in
Route


With this function, I can then calculate travel metrics from the Boston Airport to 9 other Geo locations using the following dataset:



And then I can get my Travel Distance/Time results in Power BI by calling this new function for each of the dataset's rows:




With those calculations results, I can then place them on my map visualization and use highlighted [Time Duration] slicer to control the visibility of location points depending on their travel distance from the Boston Internation Airport.




I have also created two other M functions in Power BI to help with Geo coding and Address formatting. Feel free to use them in your geo data discovery, but please make sure to update them with your own Bing Maps Key that you're going to get after a registration to use Bing Map resource.

fnAddressLocate function takes textual address as a parameter and returns Latitude and Longitude coordinates for this address location:
let 
AddressToLocate = (address as text) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Locations/"&address&"?o=xml&key=BingMapsKey")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
ResourceSets = #"Changed Type"{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Changed Type1"{0}[Resources],
Location = Resources{0}[Location],
#"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
Point = #"Changed Type2"{0}[Point]
in
Point
in
AddressToLocate

fnFormatAddress function can help you to cleanse and format an address for better Geo Location. 
For example, it can change this address line "1 Harborse D, Bostn, 02128" into this formatted/corrected version "1 Harborside Dr, Boston, MA 02128"
let 
AddressToFormat = (address as text) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Locations/"&address&"?o=xml&key=BingMapsKey")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
ResourceSets = #"Changed Type"{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Changed Type1"{0}[Resources],
Location = Resources{0}[Location],
#"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Point", "BoundingBox", "EntityType", "Address", "Confidence", "MatchCode", "GeocodePoint"})
in
#"Removed Columns"
in
AddressToFormat

In overall, I really liked enriching my Power BI  data model with web requests to Bing Map APIs. The only thing that I didn't like was a response time. On a set of 10 records, it took approximately  5 seconds for the refresh, however, I've noticed that on larger datasets it might take more time per one record to call and process web request results.

What can be done to speed up this recalculation process may be discussed in another blog post. But in the meantime, feel free to comment if you like the described approach for Travel Distance/Time calculation in Power BI using Bing Map API.
Case
I have a large XML file with Orders and Onderlines which I want to (merge) join into a new destination. To join the two outputs I need to order them, but the sort transformation takes too much time. Is there a faster alternative?
XML Source with two joined outputs

























Solution
The solution is surprisingly quite simple: the outputs are already sorted and you only have to tell SSIS that (Similar to a source with order by in the query).

For XML files with multiple levels (first for orders and second for orderlines) like below, SSIS will create two output ports.
XML Sample
















The outputs will have an extra bigint column which allows you to connect the orderlines to the correct order.
Two outputs with additional ID column














Instead of using these ID columns in the SORT transformations, you can also use the advanced editor of the XML source to tell SSIS that these columns are already sorted. Right click the XML source and choose 'Show Advanced Editor...'.
Show Advanced Editor...






















Then go to the last page 'Input and Output Property' and select the Orderline output. In the properties of this output you can tell SSIS that the output is sorted.
Set IsSorted to true
























Next expand OrderLine and then Output Columns and click on the additional ID column 'Order_Id'. In its properties locate the SortKeyPosition and change it from 0 to 1.
Set SortKeyPosition to 1

























Repeat this for the second output called 'Order' and then close the advanced editor. If you still have the SORT transformations, you will notice the yellow triangle with the exclamation mark in it. It tells you that the data is already sorted and that you can remove the SORT transformations.
And if you edit the Data Flow Path and view the metadata you will see that the column is now sorted.
Sorted! Remove the SORT transformations!




















Conclusion
The solution is very simple and perhaps this should have been the default sort key position anyway? It smells like a bug to me...
No sorts!




Case
A while ago I did a post on validating XML files in SSIS with nested XSD files. The out of the box XML Task doesn't honor nested XSD files with an (include or import). The Script Task workaround is simple, but since I use it a lot I decided to make a task for it.

Solution
The XML Validation Task allows you to specify the XML and XSD filepaths with a Connection Manager or string variable. After that it will either succeed or fail with an error message describing what's wrong with the XML file.
XML Validation Task V0.1

















Please email me (address is under the Help button) bugs and feature requests for this task.
Items to address:
  • More validation to make it really monkey proof
  • Different icon
Thinking about:
  • Providing multiple XSD's (but how many?)
  • Providing XML and/or XSD content (not path) via string variable
  • Option to throw warning instead of error






Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008, 2012, 2014 and 2016 version on the download page.

V0.1 Initial version for 2008 to 2016
V0.2 ? (Leave a comment)

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to restart BIDS because it caches the GAC on startup. Restarting SQL Server Data Tools is not necessary.

Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom task.

Case
The path of the XSD file in the XML Source component is hardcoded, but the path on my production environment is different than my development environment. The XML source doesn't have expressions. How do I make this XSD path configurable?
XML Source Connection Manager Page
without Connection Managers



























Solution
Unfortunately the XML source component does lack some very basic functionalities like the use of a Connection Manager (although ironically it's called the Connection Manager page). The source can use variables as input, but there isn't such option for the XSD file.
XML Task can use Connection Managers


























1) Find Expressions
The XML Source Component doesn't support expressions, but the Data Flow Task itself does. Go to the properties of the Data Flow Task and locate the expressions and click on the ... button

Select Data Flow Task and press F4 to get properties


























2) Select Property
Locate the XMLSchemaDefinition property of your XML Source in the Property Expression Editor and add an expression on it by clicking on the ... button.
Property Expression Editor
















3) Add Expression
Now you can either replace its value by a variable or a parameter if you use SSIS 2012 and above.
Expression Builder
























That's it. Now you have a workaround for the absence of a real Connection Manager. An alternative could be to use a Script Component as XML source or an XML file with an inline XSD schema.
Case
I have an XML file which I want to validate against an XSD file before processing it. However my XSD contains an include (or import) tag and the XML Task will not validate it and throws an error about certain parts that are not declared.

How can I validate an XML file against an XSD with an include (or import) tag?

Solution
Although the XML source component honors includes (and imports), the XML task doesn't. See(/vote for) this Microsoft Connect feedback.

You can use a Script Task to validate your XML file against the XSD file. For this example I use three files: movies.xmlmovies2.xsd and movies2_include.xsd.
<?xml version="1.0"?>
<movies>
<movie id="123">
<title>Pulp Fiction</title>
<year>1994</year>
<rating>8.9</rating>
</movie>
<movie id="124">
<title>Memento</title>
<year>2000</year>
<rating>8.6</rating>
</movie>
<movie id="124">
<title>The Usual Suspects</title>
<year>2000</year>
<rating>8.6</rating>
</movie>
</movies>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:include schemaLocation="movies2_include.xsd"/>

<xs:element name="movies">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="movie">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="title" type="LimitedString" />
<xs:element minOccurs="0" name="year" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="rating" type="xs:decimal" />
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:simpleType name="LimitedString">
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:schema>

1) New File Connection
I will use Connection Managers in this example to make things easier to configure. Feel free to use something else such as variables. Right click in the Connection Managers pane and select "New File Connection...".
New File Connection for XML and XSD file






















Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file (movies2.xsd) so that you have two Connection Managers named movies.xml and movies2.xsd. You don't have to create a Connection Manager for the include xsd, because it's included by code.
File Connection Manager Editor













2) Script Task
Add an Script Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.
Script Task to validate XML






















3) The script
Edit the Script Task and choose your Script Language, hit the edit button to edit the script and copy the code from the main method below to your main method and add the import/include row.
// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml; // Added
#endregion

namespace ST_d2a4d346c17d488da0cd327617293c52
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// create object for the XSD file that will be used
// for validating the XML file. Use the Connection
// Manager to get the path from the XSD file.
XmlReaderSettings xmlrs = new XmlReaderSettings();
xmlrs.ValidationType = ValidationType.Schema;
xmlrs.Schemas.Add(null, Dts.Connections["movies2.xsd"].ConnectionString);
xmlrs.Schemas.Compile();

// Try reading the XML file using the XSD. Use the
// Connection Manager to get the path from the XML file
try
{
XmlReader xmlr = XmlReader.Create(Dts.Connections["movies.xml"].ConnectionString, xmlrs);
while (xmlr.Read())
{
}
// The XML file was succesfully read.

// Close XML file
xmlr.Close();

Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Validation failed, fire error event with errormessage
Dts.Events.FireError(-1, "Validate XML", "Validation error: " + ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

#region ScriptResults declaration
///
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
///

enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
or VB.net
' VB.Net Code
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml ' Added
#End Region

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Public Sub Main()
' create object for the XSD file that will be used
' for validating the XML file. Use the Connection
' Manager to get the path from the XSD file.
Dim xmlrs As New XmlReaderSettings()
xmlrs.ValidationType = ValidationType.Schema
xmlrs.Schemas.Add(Nothing, Dts.Connections("movies2.xsd").ConnectionString)
xmlrs.Schemas.Compile()

' Try reading the XML file using the XSD. Use the
' Connection Manager to get the path from the XML file
Try
Dim xmlr As XmlReader = XmlReader.Create(Dts.Connections("movies.xml").ConnectionString, xmlrs)
While xmlr.Read()
End While
' The XML file was succesfully read.

' Close XML file
xmlr.Close()

Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Validation failed, fire error event with errormessage
Dts.Events.FireError(-1, "Validate XML", "Validation error: " + ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub

#Region "ScriptResults declaration"
'This enum provides a convenient shorthand within the scope of this class for setting the
'result of the script.

'This code was generated automatically.
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

#End Region

End Class

Note: For those using SSIS 2005: use the taskresult row from 2005 that can be found in your main method and replace the followin lines: C# line 34 & 40 / VB.Net line 31 & 35.


4) Testing
Run the package. Change something in the XML file to make it fail (a movie title longer than 50 chars).
Test result










Case
I have an XML file which I want to validate before processing it. How can I validate an XML file against an XSD file with SSIS?

Solution
There are two solutions in SSIS for validating an XML file.
A) XML Task, but the big downside is that it can't validate against XSD files with import or include tags. See(/vote for) this Microsoft Connect feedback.
B) Script Task, but that requires some .Net knowledge.

In this post I will elaborate the XML Task solution and in the next post I will workout the Script Task solution.

XML Task
For this example I use two files: movies.xml and movies.xsd
<?xml version="1.0"?>
<movies>
<movie id="123">
<title>Pulp Fiction</title>
<year>1994</year>
<rating>8.9</rating>
</movie>
<movie id="124">
<title>Memento</title>
<year>2000</year>
<rating>8.6</rating>
</movie>
<movie id="124">
<title>The Usual Suspects</title>
<year>2000</year>
<rating>8.6</rating>
</movie>
</movies>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:simpleType name="LimitedString">
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>

<xs:element name="movies">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="movie">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="title" type="LimitedString" />
<xs:element minOccurs="0" name="year" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="rating" type="xs:decimal" />
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

1) New File Connection
I will use Connection Managers in this example to make things easier to configure. Feel free to use something else such as variables. Right click in the Connection Managers pane and select "New File Connection...".
New File Connection for XML and XSD file






















Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file so that you have two Connection Managers named movies.xml and movies.xsd
File Connection Manager Editor













2) XML Task
Add an XML Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.
XML Task






















3) XML Task editor
Edit the XML Task and select the XML Connection Manager as input and the XSD Connection Manager as Second Operand. OperationType should be Validate and ValidationType should be XSD.
Set XML Task editor for validation






















4) Testing
Run the package. Change something in the XML file to make it fail (a movie title longer than 50 chars).
The result

















Note: If you try to validate against an XSD with an include or import, you will get an error that certain parts are not declared. The next post will be about validating an XML file against an XSD file with a Script Task.
Case
I want a Foreach Folder Enumerator, but the Foreach Loop component only loops through files.

Solution
Unfortunately this isn't supported by the standard Foreach Loop component, but there are a couple of workarounds:
- Use an open source component Directory Foreach Enumerator or my own Custom Foreach Folder Enumerator
- Use a Script task to accomplish this.

This example uses the Script task and a Foreach Loop to enumerate through the folders. The Script tasks generates a list of Directories and the Foreach Loop loops through that list.

1) Control Flow
Drag a Script task and a Foreach Loop container to the Control Flow like the image below.
Control Flow




















2) Variables
Create three string variables:
  1. startlocation that indicates the parent folder wherein we gonna search for subfolders. Fill it with a path like "d:\foreachfoldertest\".
  2. xmldoc which we gonna use for communicating between the Script task and the Foreach Loop.
  3. folder which is used for the variable mapping in the Foreach Loop container.
Varibles (right click in Control Flow)










3) Script Task
Select the startlocation as a readonly variable (we only read the path inside it) and select xmldoc as a readwritevariable (an xml string will be stored in it).
Variables



















4) The Script
Copy the following script to your Script task
// C# code 
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO; // Added
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Get all subfolders within the parent folder
string[] subFolders = Directory.GetDirectories(Dts.Variables["User::startlocation"].Value.ToString());

// Create variables for the XML string
XmlDocument xmldoc = new XmlDocument();
XmlElement xmlRootElem;
XmlElement xmlChildElem;
XmlText xmltext;

// Add the root element: <ROOT>
xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

// Variable for directory information
DirectoryInfo directoryInfo;

// Loop through the parent folder
foreach (string currentFolder in subFolders)
{
// Fill directoryInfo variable with folder information
directoryInfo = new DirectoryInfo(currentFolder);

// Create the child element that contains the path:
// <Folder>d:\foreachfoldertest\subfolder1\</Folder>
xmlChildElem = xmldoc.CreateElement("", "Folder", "");
xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
xmlChildElem.AppendChild(xmltext);

// Add the child element to the root element
xmlRootElem.AppendChild(xmlChildElem);
}
// Add the root element to the xml document
xmldoc.AppendChild(xmlRootElem);

// Store the xml in the SSIS variable
Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

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

This script will result in:
<ROOT>
  <Folder>d:\foreachfoldertest\subfolder1</Folder>
  <Folder>d:\foreachfoldertest\subfolder2</Folder>
  <Folder>d:\foreachfoldertest\subfolder3</Folder>
</ROOT>

5) Foreach Loop Container
Edit the Foreach Loop Container and change the enumerator on the Collection tab to Foreach NodeList Enumerator. This means that it will loop through an xml string. Change the DocumentSourceType to Variable and select the xmldoc variable as the DocumentSource. This is the variable that contains the xml string from the Script task. Last... add the following string to the OuterXPathString: "/ROOT/*" (without the quotes). This means that it's gonna look within the root element.
Foreach NodeList Enumerator



















6) Variable Mappings
Map the variable folder to Index 0. This will store the path in this variable.
Variable Mappings



















7) Test the result
Now you can add your own tasks to the Foreach Loop container and use the variable folder to get the path. I added a simple Script Task for testing that shows the path in a MessageBox.
Example with MessageBox














8) More advanced script
You can even add a Traverse subfolders option with this recursive method.
// C# code
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO; // Added
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

// Variables for the xml string
private XmlDocument xmldoc;
private XmlElement xmlRootElem;

public void Main()
{
// Inialize XMLdoc
xmldoc = new XmlDocument();

// Add the root element: <ROOT>
xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

// Add Subfolders as Child elements to the root element
GetSubFolders(Dts.Variables["User::startlocation"].Value.ToString());

// Add root element to XMLdoc
xmldoc.AppendChild(xmlRootElem);

// Fill SSIS variable with XMLdoc
Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

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

// Recursive method that loops through subfolders
private void GetSubFolders(String parentFolder)
{
// Get subfolders of the parent folder
string[] subFolders = Directory.GetDirectories(parentFolder);

// XML child element
XmlElement xmlChildElem;
XmlText xmltext;

// Variable for file information
DirectoryInfo directoryInfo;

// Loop through subfolders
foreach (string currentFolder in subFolders)
{
// Fill fileInfo variable with file information
directoryInfo = new DirectoryInfo(currentFolder);

// Create child element "Folder":
// <Folder>d:\foreachfoldertest\subfolder1\</Folder>
xmlChildElem = xmldoc.CreateElement("", "Folder", "");
xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
xmlChildElem.AppendChild(xmltext);

// Add child element to root element
xmlRootElem.AppendChild(xmlChildElem);

// Recursive call
GetSubFolders(directoryInfo.FullName);
}
}
}
}

Note: there are more options to accomplish the communication between the Script Task and the Foreach Loop container, but I wanted you to show an infrequently used option of the Foreach Loop container. Let me know what your solution was.
Case
A few month ago I had a job to add some adjustments to a SSIS project, but they lost the Visual Studio project file. So I created a empty project and started adding all the packages (over a 150) from SQL Server to the project one by one. After five packages I realised this could take ages. A while ago I created a package to upload all Visual Studio project files to SQL Server and thought I can do that vica versa!

Solution
I created a package with a single Script Task and used the SSIS API to download all packages to the Visual Studio SSIS project folder. After that I edited the Visual Studio SSIS project file (.dtproj, an XML file) to add the packages to the project itself.
// C# code
// Script to download packages from a SQL Server and add them
// to a Visual Studio SSIS Project file. The script assumes
// that the package name and package object name are equal.
using System;
using System.Data;
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fe8263e907c94504a4c41974a46fc623.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
/////////////////////////////////////////
// The Project file and folder
String ProjectFilePath = @"d:\Integration Services Project\Integration Services Project.dtproj";
String ProjectFolderPath = ProjectFilePath.Substring(0,ProjectFilePath.LastIndexOf(@"\")+1);

// The SQL Server
String SSISServer = "Name_Of_Your_Server";

/////////////////////////////////////////
// Variable that contains the Visual Studio SSIS Project file, an XML document
XmlDocument doc = new XmlDocument();
doc.Load(ProjectFilePath);

// Get DTSPackages to add a package
XmlNode DTSPackages = doc.SelectSingleNode("/Project/DTSPackages");

/////////////////////////////////////////
// Set application context
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

// Create a package variable to temporary store the server packages
Package serverPackage;

/////////////////////////////////////////
// Loop through packages and folders in the root: / (or change the path for your folder)
foreach (PackageInfo serverPackageInfo in app.GetPackageInfos("/", SSISServer, null, null))
{
// Get only the packages. You could make a recursive function to loop through all folders
if (serverPackageInfo.Flags.ToString().ToLower().Equals("package"))
{
/////////////////////////////////////////
// Fill the package variable with a server package
serverPackage = app.LoadFromSqlServer(serverPackageInfo.Folder + serverPackageInfo.Name, SSISServer, null, null, null);

// Save the package to xml (.dtsx) in the project folder.
app.SaveToXml(ProjectFolderPath + serverPackageInfo.Name + ".dtsx", serverPackage, null);

/////////////////////////////////////////
// Dirty/lazy check to see if the package already exists in the project file (can't add them twice)
bool fireAgain = true;
if (DTSPackages.InnerXml.Contains(serverPackage.Name + ".dtsx"))
{
Dts.Events.FireInformation(0, "Download package", "Existing local package had been overwritten by Server package", string.Empty, 0, ref fireAgain);
}
else
{
/////////////////////////////////////////
// Add package to project xml file
// <dtspackage formatversion="3"></dtspackage>
XmlNode newDtsPackageNode = doc.CreateNode(XmlNodeType.Element, "DtsPackage", "");
XmlAttribute newDtsPackageAttribute = doc.CreateAttribute("FormatVersion");
newDtsPackageAttribute.Value = "3";
newDtsPackageNode.Attributes.Append(newDtsPackageAttribute);

// <name>Datastaging.dtsx</name>
XmlNode newDtsPackageNameNode = doc.CreateNode(XmlNodeType.Element, "Name", "");
newDtsPackageNameNode.InnerText = serverPackage.Name + ".dtsx";

// <fullpath>Datastaging.dtsx</fullpath>
XmlNode newDtsPackageFullPathNode = doc.CreateNode(XmlNodeType.Element, "FullPath", "");
newDtsPackageFullPathNode.InnerText = serverPackage.Name + ".dtsx";

// <references />
XmlNode newDtsPackageReferencesNode = doc.CreateNode(XmlNodeType.Element, "References", "");

// Add child nodes to <dtspackage>
newDtsPackageNode.AppendChild(newDtsPackageNameNode);
newDtsPackageNode.AppendChild(newDtsPackageFullPathNode);
newDtsPackageNode.AppendChild(newDtsPackageReferencesNode);

// Add <dtspackage> to <dtspackages>
DTSPackages.AppendChild(newDtsPackageNode);
Dts.Events.FireInformation(0, "Download package", "Package from server has been added to the project.", string.Empty, 0, ref fireAgain);
}
}
}
/////////////////////////////////////////
// Save project
doc.Save(ProjectFilePath);

Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
I hard coded the project file and server name, but you can change that by using variables and configure those.