As Allen said you can not use Data Flow task because it will not support dynamic metadata, but you can use a foreach to loop through flat files, then put an execute sql task inside foreach loop and use BULK INSERT command to import data from flat files and in this way you shouldn't be worry about dynamic mappings.http://www.rad.pasfu.com

We need to set up a system that can dynamically map a text file source to a SQL table destination. As much as we would like it clients never seem to be able to submit files according to the spec. Usually the columns are in the wrong order and the headers a different. Is there a way that SSIS can handle this without having to programmatically build a task? Or is there other "pre-ETL"software that can modify the text file or other vendors that allow you to dynamically configure the mappings?

Unfortunately, SSIS can't handle this automatically now. But if the column collection is stable and only the order is different, I would suggest you to use a Script Source to extract data from the data file instead of building a new task/component. Regards, XiaochenXiaochen Wu

Usually the columns are in the wrong order and the headers are different. I think that your main problem is the Heareds names been different, when we have such scenarios what the company does is fix a contract that has 1- ETL standard 2- Header names must not change 3- if its a CSV or text file , the order of the column must not change If the customer can't stick to the contract we suggest that we will make a small DB like Access or a ETL package so that it sticks to the standard of the ETL and the customer can use that tool on it's own computer i.e for Access is make a form that has a FROM DATE and a TO DATE and a OK button that will produce the data within that date range and make a Table in Access acording to the ETL standard that we have for it. sometimes you have to help your customer to make the SOURCE data. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).