Hi, I have an excel with multiple sheets. The coulmn names in each excel sheet is different. This excel sheet is updated by quality team and hosted in a location. Since this excel is hand tweaked there are possibilities to have column name changes and sheet name changes. My reqirement is to pull the data from every excel sheet to an individual table in SQL server. An excel sheet maps to one table in sql server. Please suggest me a way to handle this in SSIS, Since the metadata changes for every sheet what is the way to use the DFT with excel source dynamically?

SSIS cannot handle dynamic metadata changes in the file format. You would have to use a script component to load data in the above scenario where the sheet names and column names change dynamically. Refer this as a reference for structuring your solution.http://btsbee.wordpress.com/

Hi btsbee, Thanks for you reply. But i was able to progress to a level wherein i could get the excel sheet names and the metadata for all sheet names...the challenging part here is to create a DFT which has Excel source that keeps changing its metadata..any help on runtime wrapper class or pipeline wrapper class to create the DFT through code would be really helpful...Thanks...

Hello, Check out the thread below. It may help you. http://social.msdn.microsoft.com/Forums/lv/sqlintegrationservices/thread/77e81670-dd2a-4d3d-95f2-2dd7274dce2e

Hi, Check out the thread below. It may help you. http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package

Hi Sivaraman, You can achieve this either by script task only or by foreachloop conatainer. Using ForEach Loop container , you can loop through all the sheets of an Excel file with help of varaibles. Kindly go through the below link...it will give your answer (some changes required as per your requirement) http://danajaatcse.wordpress.com/2010/05/05/extracting-data-from-multiple-sheets-in-an-excel-file-in-ssis/ My frnd, i'll be back with step by step solution for this...soon :) Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

As I said before, its not possibe to use the excel source which will dynamically adjust its metadata based on changes to the files. Having said that using a script component as a source and parsing your dynamic excel file will be the way to go. Refer this which gives a detailed approach on using scripting component fo importing data. This link goes one step further wherein even the excel sheet names are dynamic.http://btsbee.wordpress.com/

As I said before, its not possibe to use the excel source which will dynamically adjust its metadata based on changes to the files. Having said that using a script component as a source and parsing your dynamic excel file will be the way to go. Refer this which gives a detailed approach on using scripting component fo importing data. This link goes one step further wherein even the excel sheet names are dynamic.http://btsbee.wordpress.com/

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 and supports dynamic columns at runtime. No programming skills are required.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Thanks a lot guys!!! I will try your suggestions and let you know about the status...Thanks a lot!!!