none
How to loop through several files in several folders on SSIS? RRS feed

  • Question

  • I've got the following directory structure:

    Folder A

      • file_Inbound.txt

    Folder B 

                   file_Oubound.txt

    I've used a for each loop in SSIS in order to iterate over this directory structure and, for each text file named exactly file_Inbound.txt and file_Outbound.txt I want to import data into 2 destination tables dbo.File_Inbound and dbo.File_Outbound tables

    I created the for each loop  but when I run the package it stops when reaching the first file. Also, I've checked the "Traverse sub-folders" box.

    I know that I have mentioned Folder A and it iterating files in Folder A but how to I need to loop through Folder A and Folder B and export the data into 2 tables . Can you please suggest me how to achieve this?

    Thanks in advance!

    Friday, May 17, 2019 5:52 AM

All replies

  • Hi mohan1111, 

    Which is the folder path mapped in the Foreach Loop Container? Folder A or the Root Folder, which includes Folder A and Folder B?

    If it's Folder A, Foreach Loop Container will not iterate through Folder B. 

    If it's the Root Folder, when you choose "Traverse sub-folders", Foreach Loop Container will iterate through both Folder A and Folder B.

    And the "Files" option will be something like: file_*bound.txt. 

    If the two table have different structure, you will need to use BCP to load data into the tables using dynamic command in Execute Process Task.  


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 17, 2019 6:56 AM
  • Hi Yang,

    ex : C:\Folder A

    C:\Folder B

    both are separate they are not in the root folder

    Friday, May 17, 2019 8:03 AM
  • Hi mohan1111, 

    It seems that you can choose C:\ as root folder. 

    Or, you can copy all the files in Folder B to Folder A, using File System Task, before iterate through Folder A. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 17, 2019 8:17 AM
  • Or you can simply using two Each Loop. One for inbound and one for outbound.  
    • Proposed as answer by Yang.Z Friday, May 17, 2019 9:38 AM
    Friday, May 17, 2019 8:37 AM
  • we can't do that it is client specified location
    Friday, May 17, 2019 8:47 AM
  • I don't understand, what do you mean?
    Friday, May 17, 2019 8:51 AM
  • Hi mohan1111, 

    Do you mean you can't copy the files?

    Then just create two separate Foreach Loop Container as DIEGOCTN said, that should work. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 17, 2019 9:39 AM
  • Hi Yang,

    I'm following this example

    http://www.learnmsbitutorials.net/for-loop-in-ssis.php

    • Proposed as answer by Yang.Z Tuesday, May 21, 2019 2:25 AM
    Friday, May 17, 2019 9:50 AM
  • Hi mohan1111, 

    Thanks for sharing the link, that's useful. 

    So the Foreach Loop Container is embed in a For Loop, and the Directory will be changed dynamically. 

    Please check the settings in the For Loop container, make sure the For Loop Container will really loop twice. 

    You can also share the screenshots in your package, so that we can help you fix it. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 17, 2019 10:01 AM