ForEach Loop Container

Image Posted on Updated on

When you need to read through a file directory and save all of the file names to a text file, follow these steps.

Step 1: Create a variable to hold the file name that the ForEach Loop reads

Step 2: Create a Flat File Connection

File Connection Manger

Step 3: Drop a “Foreach Loop Container” to the Control Flow

Foreach Loop Container

 Step 4: Rename the “Name” text to something more meaningful

Foreach Loop Editor General

Step 5: In the Enumerator drop-down, select “Foreach File Enumerator” and pick the folder path. Note: If you check “Traverse subfolders”, the Foreach loop will go through all subfolders.

Foreach Loop Editor Collection

Step 6: We need to save our results to a variable if we want to know what files were scanned. Under the “Variable” drop-down, select “User::MyFileName”

Foreach Loop Editor Variable Mappings

 Step 7: Drop a “Script Task” into the Foreach Loop Container.

Script Task

Step 8: Double click on the “Script Task”. In the “ReadOnlyVariables”, select “User::MyFileName”. Click on “Edit Script”

Script Task Editor Script

Step 9: Add the following C# code

public void Main()
ConnectionManager cm = Dts.Connections[“Output”];

string content = Dts.Variables[“User::MyFileName”].Value.ToString();

string fileDirectory = cm.ConnectionString;

System.IO.File.AppendAllText(fileDirectory, content + “\r\n”);

Dts.TaskResult = (int)ScriptResults.Success;

 Now run your SSIS and once completed, open up the Output.txt file to see the results.