locked
question on for each loop container RRS feed

  • Question

  • Hi,

    I am trying to perform the below task but not sure on how to proceed

    Export around 20 SQL Server Tables in to same Excel File but multiple tabs. Right I have created seperate data flow tasks for exporting each table in to that spreadsheet. If I continue this then I will end up creating 20 different data flow tasks.

     

    How can I use For each loop container to achieve this ? I am thinking to pass table names and spread sheet names dynamically and use for each loop container. But I am not sure on how to do this ? I have table names like QC001, QC002, QC003 . Excel spreadhseet names are also same .

    Sunday, June 28, 2009 3:13 PM

Answers

  • Do the tables all have the same meta data?  i.e. are they all the same structure?  If so, you can do exactly as you say and use a for each loop with a list of tables you would like to export.  If not, you will have to go about it either programatically (not an easy task) or via a data flow task per table as you are doing now...

    Hope this helps.

    Please mark answered posts. Thanks for your time.
    • Proposed as answer by Sudeep Raj Monday, June 29, 2009 11:20 AM
    • Marked as answer by Eric Wisdahl Tuesday, July 7, 2009 12:26 PM
    Monday, June 29, 2009 12:12 AM

All replies

  • Hi..

    I believe the data flow task where you have Excel as a destination will always create a new sheet. To do what you want, you may need to employ a script task inside the for each loop. With each iteration of the script task, you can add a new tab. This however, may be slow. Alternatively, keep the code you have, create 20 excel files. After you complete the for each loop, add a script task to combine the 20 sheets into one workbook.

    JP
    Sunday, June 28, 2009 6:17 PM
  • Do the tables all have the same meta data?  i.e. are they all the same structure?  If so, you can do exactly as you say and use a for each loop with a list of tables you would like to export.  If not, you will have to go about it either programatically (not an easy task) or via a data flow task per table as you are doing now...

    Hope this helps.

    Please mark answered posts. Thanks for your time.
    • Proposed as answer by Sudeep Raj Monday, June 29, 2009 11:20 AM
    • Marked as answer by Eric Wisdahl Tuesday, July 7, 2009 12:26 PM
    Monday, June 29, 2009 12:12 AM