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
Step 3: Drop a “Foreach Loop Container” to the Control Flow
Step 4: Rename the “Name” text to something more meaningful
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.
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”
Step 7: Drop a “Script Task” into the Foreach Loop Container.
Step 8: Double click on the “Script Task”. In the “ReadOnlyVariables”, select “User::MyFileName”. Click on “Edit 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.