SSIS – Starter kit for the uninitiated Foreachloop – for each file enumerator

In this post we continue from where we left off in the last post, with the xml files now formatted properly it’s time to load the data into the database. To achieve this I am going to use the foreach loop container once more and this time use the for each file enumerator.

Drag and drop a Foreachloop container and name Loop over XML Files

Then drag and Drop a Data Flow task into it. The data flow task will be used to load the file into the database.

Follow this up with a File System task which will delete the file after it’s been processed. Naturally you can also move the file to another folder instead of deleting it.

Right click anywhere in the background and choose variables and define a variable called “xmlpath”. This variable will contain the path of the xml file that needs to be loaded.

Double click and open the foreachloop enumerator and configure it as shown.

1 Make sure the Foreach File Enumerator is selected

2 provide the path to the folder where the xml files can be found.

3 Mention that we are only looking for xml files

4 We need the fully qualified path so that the DFT knows where to look

5 When done with the collections screen we need to map the file path to our newly created variable xmlpath which can be found under the variables mappings page.

Under Variable Mapping make sure the Index 0 is assigned to the Variable xmlpath

Press OK

Next we move on to the Data Flow Task