That will only work if all sheets have the exact same columns. I guess this is not the case. If your metadata is dynamic, you need to create a dataflow for each Excel sheet.MCTS, MCITP - Please mark posts as answered where appropriate.

yeah i am sure both the names are same i recreated the file and copied the same name from the first sheet. Am i missing anything else? -Eshwar

sorry it is working there is blank sheet that was causing the issue. Thanks Eshwar

Hi Team, I am facing an issue with the load of excel file with multiple sheets. Here is the link with i followed to do this: http://www.sqllion.com/2009/06/programming-foreach-loop-container-%E2%80%93-enumerating-excel-sheets/ It is getting executed for the first sheet but from the second sheet on it is failing with the below error: [SSIS.Pipeline] Error: "component "Excel Source" (37)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". additional info: [Excel Source [37]] Warning: The external columns for component "Excel Source" (37) are out of synchronization with the data source columns. The column "F1" needs to be added to the external columns. The external column "Capital" (76) needs to be removed from the external columns. The external column "State" (73) needs to be removed from the external columns. Thanks in advance,Eshwar.

That will only work if all sheets have the exact same columns. I guess this is not the case. If your metadata is dynamic, you need to create a dataflow for each Excel sheet.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Eshwar, You might be mis-matching the columns of different sheet. Please check and verify once that you are not having such basic issues. While, you can also check URL, http://danajaatcse.wordpress.com/2010/05/05/extracting-data-from-multiple-sheets-in-an-excel-file-in-ssis/ Revert if prblm still persists..!!Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

Thanks for the reply.. the column name of all the sheet are same. -Eshwar

Are you sure? The error says columns Capital and State are not present in the current sheet.MCTS, MCITP - Please mark posts as answered where appropriate.