Hi,

I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database. What would be the best approach to carry out this task.I  saw so many conflicting opinions on similar kind of requirement, but unable to decide. If somebody can give an accurate approach, it would be helpful. Whether i need to create separate data flow task for each file or separate package?

In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.

• Edited by Thomas_ITsavvy Tuesday, June 16, 2015 6:25 PM

use for loop container .

http://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/

The standard Data Flow Task supports only statically defined columns metadata defined at design time. This means you have to create separate Data Flow Task for each table you want to process.

I would recommend you check the commercial COZYROC Data Flow Task Plus. This is an extension of standard Data Flow Task with support for dynamic columns metadata at runtime. You can process all your tables using single Data Flow Task Plus setup inside For Each Loop (FEL) container. You can also use any transformation you want in your dynamic data flow. No programming skills are required.

You will find your answer here.

https://www.youtube.com/watch?v=1WXKpkwjhX8

Hi Thomas,

According to your description, you want to load multiple flat files to multiple tables. If the tables having the same structure but differing in the name, we can use dynamic Flat File Connection Manager and OLE DB Connection Manager in the Foreach Loop Container to achieve your requirement. The following similar blog is for your reference:
http://sqlage.blogspot.com/2013/12/ssis-load-multiple-files-to-table-with.html

If the tables don't have same structure, the following similar issue about How To Load Files in Different Tables According To The Name Of File is for your reference:
http://sqlage.blogspot.jp/2014/10/ssis-how-to-load-files-in-different.html

Or we can directly create separate data flow task for each file importing.

Thanks,
Katherine Xiong