i have multiple folders in a directory and each folder contains multiple files of same extension but with different formats(columns) and names(xmp: file aand file b). We have a data task in which we are joining(merge) both files and loading into table.

I am not able to understand how to achieve this. should i use foreach, but then it takes 1 file at a time and i need the other file also to join it in data flow.

can somebody help me on this.

 

This is doable if you can find some file naming principles that 1) identify a set of files as being in some way one set; 2) identify the specific load associated with each file.

What you do is you find files that match one pattern, say "XYZ*.txt". That becomes your loop.  You parse the file name using either an expression or script task to find the part of the name that identifies the set of files (normally a date stamp).  Then for each of the other files, you need the file pattern for each file + the set identifier.

Hi scottichrosaviakosmos,

According to your description, you want to load data from multiple files to a SQL Server table.

If in this scenario, those multiple files should have same columns. Then we can use a Data Flow Task within a Foreach Loop Container to achieve your requirement. For more details, please refer to the following blog:
http://reports4u.co.uk/ssis-import-multiple-files-into-a-table/

Besides, since SSIS Source component only support static metadata, please format those files with same columns in advance if they have different columns.

Thanks,
Katherine Xiong

If each type of files metadata is fixed then you can use separate for each loops to parse each files and get data to a staging table. Then assuming you've common fields based on which tables can be related you can use a sql procedure to merge the data from tables and insert to your final table.