September 25, 2018 at 9:22 am
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 .
September 25, 2018 at 10:01 am
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
September 25, 2018 at 11:29 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply