1

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.

Improve this question

2 Answers 2

Reset to default
0

You said you have the directories in a object variable.

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

  6. Add the following code to CreateOuputRows

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

  7. Continue data flow to your destination.

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for the reply. It's not working. "The name "OutputBuffer" does not exist in the current context.
sorry it is Output0Buffer. I just typed all this out without SSIS.
Let me actually provide a full SSIS example of using a double foreach
Ok thanks. What do you mean by "it is Output0Buffer"? In my C# program, It mentions that Output0Buffer does not exist in the context.
Did you do Step 4?
|
0

My other answer is just fine and would be closer to the way I would do it completely in C#.

However, in SSIS you can do it with just objects provided:

enter image description here

Improve this answer

6 Comments

In your first ForEach: Collection -> Enumerator : Foreach ADO Enumerator ? (right) + Expression : What do you write ? + ADO Object Source Variable : ? Variables Mappings : ? ||||| In your second ForEach (sub For Each) Collection -> Enumerator : Foreach ADO Enumerator ? (is it right?) + Expression : What do you write ? + ADO Object Source Variable : ? Variables Mappings : ?
Your two foreach loops are as Foreach ADO Enumerator?
1st one is ADO object based on your object variable. The second is for each file cased on an expresion for folder based on variable from foreach #1.
thanks. i share the pictures on the post. can you please tell me if it's correct or not?
1) In the first For Each , I put Foreach ADO Enumerator then expresion : nothing (blank) and ADO Object source variable at FolderList (variable type Object that contains all the directories). Variables Mappings : User::FolderCurrent and Index at 0. Is it correct?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.