Today I was using the NodeList ForeachContainer and I wanted to extract 2 values of a node and store it in Variables. This was more difficult than I expected. I will show how we can do this by an example. So we will create directories based on a XML
Remark: We will hardcode everything in our package.
Here is the XML:
<?xml version="1.0" encoding="utf-8"?>
<Dirs>
<Dir name="aa" isReadonly="true" />
<Dir name="bb" isReadonly="true" />
<Dir name="cc" isReadonly="false" />
<Dir name="dd" isReadonly="true" />
<Dir name="ee" isReadonly="true" />
<Dir name="ff" isReadonly="true" />
<Dir name="gg" isReadonly="true" />
<Dir name="hh" isReadonly="true" />
<Dir name="ii" isReadonly="false" />
<Dir name="jj" isReadonly="true" />
<Dir name="kk" isReadonly="true" />
</Dirs>
Step1: We drag a Foreach Container on the control flow. Then we edit the ForEach Container. On the General tab we change the Enumerator to Foreach NodeList Enumerator.
Step2: Because we want to hardcode everything in the package. We need to set the follow properties documentSourceType, OuterXPathStringType and InnerXPathStringType to DirectInput. After this we can past our XML in the DocumentSource property.
Step3: What we want to do is iterating through all the node of the XML. So we need to set the EnumerationType to ElementCollection.
Step4: When you want to use the NodeList Foreach Container you will need to know something about Xpath Here is a description from W3Schools "XPath is a language for finding information in an XML document. XPath is used to navigate through elements and attributes in an XML document.". If I have problems with creating Xpath I use a tool called Visual XPath a version of this tool can be downloaded from this page.
Now that we can do a little bit pf XPath we need to apply this on our NodeList Foreach Container. We can explain the properties OuterXPathString and InnerXPathString.
The OuterXPathString is an Xpath expression that is applied on the XML.
So the XPath for retrieving all the Dir elements is the follow /Dirs/dir. So we can put this expression in the OuterXPathString property. Here is the result with Visual XPath when we apply this expression on our XML.
The result after applying the OuterXPathString on XML is a NodeList where we can do additional filtering with the InnerXpathString.
Snippet of the follow article
"The XPath expression that applies to the XML file is the outer XPath operation, stored in the OuterXPathString property. If the XPath enumeration type is set to ElementCollection, then the ForeachNodeList enumerator can apply an inner XPath expression, stored in the InnerXPathString property, to a collection of elements."
In our case we want to retrieve the current dir element. The Xpath expression for this is '.'.
So we put '.' in the InnerXPathString. Because we will store 2 attributes of a Dir element in Variables we need to set the InnerElementType to Node.
Step 5: Here we create 4 variables in SSIS.
- DirPath (String) (contains the attribute 'name' that can be found on each 'Dir' element in de xmldocument + DirectoryPath)
- IsReadOnly (Boolean) (contains the attribute 'isReadonly' that can be found on each 'Dir' element in de xmldocument)
- DirNode (System.Object) (contains a object that give us access to isReadonly and fileName attribute)
- DirTemplatePath (String) where the directories will be created.
Now we need to tell the Foreach Container to store the the current iteration in a variable we do this on the Variable Mappings of the ForEach.
Step 6: Now we will create the directories on disk. The File System Tasks can take care of this. Change the follow properties of the task:
- Operation -> CreateDirectory
- IsSourcePathVariable -> True
- SourceVariable -> DirPath
Set DelayValidation to True so that SSIS wait to validate the Task If we leave to False you will retrieve an error at Design time like 'DirPath is used as source or destionation and is empty'.
Step 7: Now we will create a task that extract the attributes from the Dir Element and store it in variables the Script Component is one of the tasks that can do this. When we use a script component we need to specify with Variables are available for the Script Task. In our case is TemplateDirPath a ReadonlyVariable and DirNode, DirPath, IsReadOnly are ReadWriteVariables. The script can be found here. Please read the comment in the script to understand.
Step 8: Finally the last step is setting the readonly attribute on the directory. The only way that I know to do this is using a script. I did not yet find how to set the Readonly Attribute dynamically. You can view the script here.
Step 9: Here the result.
The ForEach Container is realy powerful and you can do a lot with it. Thank to ashvinis for the explication how a NodeList ForEachContainer works. You can download the sample here.
Please change the DirTemplatePath Variable if you should run the sample.