SSIS How to extract files names of multiple folders using a Foreach Loop Container?

Question

I try through SSIS to extract the names of the files contained within the folder names and load into a SQL Server table. The folders names are already stored in an SSIS package variable.

I'd like to use a Foreach Loop Container that (for each iteration);

1) Take one folder name (stored in the object variable) in input.
2) Read all the files names of the folder.
3) Load the files names to a SQL Server destination table.

Does anyone have a solution?

Thanks in advance.

KeithL KeithL · Accepted Answer · 2020-04-15T15:50:02

You said you have the directories in a object variable.

Add a foreach loop (choose ADO Object) and set variable to a string variable called targetDirectory
Add a data flow
Add a script component source
Add an output called fileName as type string
Pass in variable targetDirectory as read

Add the following code to CreateOuputRows

foreach(string fName in System.IO.Directory.GetFiles(Variables.targetDirectory))
{
   Output0Buffer.AddRow();
   Output0Buffer.fileName = fname;
}

Continue data flow to your destination.

Reference https://docs.microsoft.com/en-us/dotnet/api/system.io.directory.getfiles?view=netframework-4.8

SSIS How to extract files names of multiple folders using a Foreach Loop Container?

2 Answers