HI,

I have one folder in that i have 3 files

1) customer.txt

2) employee.txt

3) student.txt

i want to load these three files three different destinations

customer file should go one destination table, employee file should go one destination table, student file should go one destination table

tomorrow if i get some more files in same folder , those files also should go separate destinations 

these should happen dynamically

please help me

• Edited by SSISJoostMVP, Moderator 14 hours 26 minutes ago Title too general. It's an SSIS forum...

Hi,

The below link will help you.

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

1. Add a foreach loop container of file enumerator pointing to directory having sub folders containing the files.

2.Add data flow task inside foreach loop container. Inside data flow task, add a flat file source and pull data from it ,

push it a temporary already created table using oldeb destionation

3. Add an execute sql task containing query that creates dynamic destination tables pulling data from temporary tables

select * into DynamicTable from TemporaryTable

Note : This will work subject to the metadata/structure of all files is the same.

The Data Flow isn't very dynamic... if all files have a different format/layout then you need to create a data flow for each file.

I think you can Loop over the folder using Foreach container, and create dataflow task programatically using ssis script task for varying colums if the existing dataflow task doesnt serve the purpose.