Hi All, I have a small problem. I have lots of text files in a folder location. Say for an example Company.txt, Code.txt. I need to insert all these files to tables similar to the file structure. Company file to Company table and Code file to Code table. The catch is that all these table structures differ one to another. How can i do this using SSIS? I guess using a for each loop with some data flow task would be a start. Can some body give me a step by step example on how i can achieve this. Thanks LM

given Steps may help. Steps 1.Take Foreach loop container where Enumerator as "Foreach File Enumerator" 2.In Enumeration configuration of Foreach loop container Set folder location and files as *.txt* 3.In variable mapping of Foreach loop take string variable as "anyName" and index as 0. 4.Take script task which decides the File Type and assign the value to common variable. 5.On the basis of FileType take diffrent path Using expression as @FileType=="AnyList" 6.Use Data flow for further steps for saving perticular file in perticular table. Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.

Please check this http://codejotter.wordpress.com/2010/04/06/importing-multiple-text-files-using-ssis/Thanks http://dwhanalytics.wordpress.com/

Hi All, Thanks Boopendra for your steps. i am bit stuck after tasks 3. Can you please elaborate more on how i can do this. Thanks for your article also _AKS. i have read that article its basically for text files that have same structure send to one table. But mine is different files to different structure. Appreciate the help guys. Help me on this more. Thanks LM

Hi lucki2005, 4.In Script Task you write logic like File you recieved is of what type,take one variable(User:Filetype) in read write variable mode in script and assign the value of file type lets say "FileAtype" to that variable. 5.connect Script task to Data Flow as Evaluation Operation as Expression and type @Filetype=="FileAtype" 6. In Data Flow Task take Flat file source and by doing basic steps insert the record in destination DB.Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.

You can follow this link and do the same for text files: http://social.msdn.microsoft.com/forums/en-us/sqlintegrationservices/thread/A3E3273C-2C90-4D7B-91DE-00F0569ECFB6 Thanks http://dwhanalytics.wordpress.com/

Hi Bhoopendra, What i did up to now is use a for each loop container and added a data flow component. then i add a flat file component with the connection as multiple flat file connection. so there will be multiple flat files being send and its dynamic. Now i want to send the correct file to the correct table in the destination. My question, can i check whether whats the file name is and make the Destination database table dynamic and pass it to the correct table? I knw we can use table name as a variable but not sure how to do it exactly. My file names and table names are the same. Thanks Lucki

Lucki, Check the link http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx here fileWeJustFound variable will have file name.Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.

Hi Bhoopendra, Thanks for you continuous support. I have now got the dynamic file name. What i want to do is get the corresponding table name from the database dynamically and map the file to the table in the data flow task. How can this be achieved Bhoopendra? Thanks Lucki

It is very complicated to accomplish the above requirement using the standard Data Flow Task. You have to essentially setup a separate task for each table layout. If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task, with ability to do dynamic data flows at runtime. You can load all your tables and layouts with only one Data Flow Task. The other benefit is the solution doesn't require programming.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

It is very complicated to accomplish the above requirement using the standard Data Flow Task. You have to essentially setup a separate task for each table layout. If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task, with ability to do dynamic data flows at runtime. You can load all your tables and layouts with only one Data Flow Task. The other benefit is the solution doesn't require programming.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Lucki, Check the link http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx here fileWeJustFound variable will have file name.Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.

It is very complicated to accomplish the above requirement using the standard Data Flow Task. You have to essentially setup a separate task for each table layout. If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task, with ability to do dynamic data flows at runtime. You can load all your tables and layouts with only one Data Flow Task. The other benefit is the solution doesn't require programming.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hi All, Thanks for all the support given.I manage to do it. Really appreciate u peoples effort and help Thanks Lucki