how to load multiple source folders related files into destination table using single foreachloop ssis package.

  • Hi I have one doubt in ssis,

    how to load multiple source folders related files into destination table using single foreachloop ssis package.

    source files are available in two different folder and all files related structure is same in the both folder.
    source file have paths :
    c:\chenlocation\ in the source path have files like : emp.txt,emp1.txt,emp2.txt
    c:\punelocation\ in the source path have files like : emp_pune.txt,emp_pune1.txt,emp_pune2.txt,emp_pune4.txt.

    in the two source path (chenandpunelocation paths)all files structure are same and look like columns
    id,name,sal.
    destination table (sql server ) is : emp and columns are same id,name,sal

    in ssis package I have implemented like below :
    declare variables :

    chenlocationpath : c:\chenlocation\
    filename   : emp.txt
    punelocationpath : c:\punelocation\

    afte that drag and drop foreachloop conatiner and type >foreachfileenumerator>directory >checnloationpath variable>filetype>*.txt
    >variable mapping >filename variable.
    after that drag and drop dft task and confiugre flatfile source and change that datatype after that destination sql server table is configured.

    after that again drag and drop 2nd foreachloop container and type >foreachfileenumerator>directory >punelocationpath variable>filetype>*.txt
    >variable mapping >filename variable.
    after that drag and drop dft task and confiugre flatfile source and change that datatype after that destination sql server table is configured.
    after exectuction all records are loaded in the destination table without failure.

    here I have used 2 foreachloop tasks to process two different source path .
    I want to impleemnt single foreachloop with two different folder path into destination table

    can you please tell me how to load two different path of source files data into destination table using single foreachloop container in ssis package .

  • A foreach loop container can handle a collection object, so you could use a script task to get file lists from both folders and populate some kind of List object, then save that to a package Object variable.  The loop task can then go through all the filenames in the package variable collection.

    This link shows an answer to a very similar question:
    https://stackoverflow.com/questions/28192083/ssis-write-to-object-variable-through-script-task

  • Scott's idea should work. A couple of other ideas come to mind.
    1) Put the FOREACH loop inside a FOR loop (looping around the different folders)
    2) Create a master package which calls the existing package twice, passing the folder name as a parameter.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply