HI, i have 5 excel files (the number can go up in the future.) and i have load all the files into 1 Destination table. What I did : i have created a package to do this. i used a data flow task inside the for each loop container, so that , 1 xls file is picked at a time and is loaded into the table. The Issue : all the excel sheets has different number of columns. (say 1st excel sheet has 5 columns and the 2nd has 8 and the 3rd has 10..) when i make 1 excel sheet as default and set the mapping, by the time the second excel sheets comes in place, some of the source columns meta data is missing. is there a way i can map the column metadata dynamically. Thanks for your help in advance. -Vinod ----- Vinod

Hello, for this situation you have to use Script task. Excel Source can not handle dynamic column change. Thankshttp://sqlage.blogspot.com/

· Yes, this is not possible through the data flow task. · The metadata needs the adjustment during the runtime. · This is possible in 3 ways : · Method 1 : 1. Loop through each work sheet. 2. For each work sheet , loop through the source columns and build a select query. 3. For each work sheet ,Loop through the destination columns and build an insert query. 4. Concatenate the SELECT and the INSERT queries and run the command for each work sheet. 5. The tables have to be different for each worksheet. · Method 2 : 1. SSIS API programming. 2. http://msdn.microsoft.com/en-US/library/ms345167(v=SQL.90).aspx. This has some data flow task programming which helps in this situation.( I applied this but in vain but rather very close but could be achievable.) But I recommend Method 1 or 3 . · Method 3 : Using OPENROWSET as in Reza’s post here ; http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/87d81376-b156-4ed7-a423-142838820078/#67555868-1579-41da-90ed-259d4e5188e6Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.