none
how to load excel sheet

    Question

  • in source i have three tables

    and i want to load each table in each sheet of one excel file i.e 3 sheets

    can any one tell me how we can do it?


    • Edited by v60 Tuesday, February 28, 2012 5:08 AM
    Tuesday, February 28, 2012 5:07 AM

Answers

  • If you have 3 fixed sheets that needs to be populated from 3 different SQL table, i would make a SMAPLEEXCELFIEL.xls insted of making the sheets on the fly.

    if you make it on the fly , you have more work to do, and you need to know SSIS a little bit more, pluse once you make it on the fly please check the excel fields data type , sometimes it doesn't display what you want.

    good luck


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Proposed as answer by Eileen Zhao Wednesday, February 29, 2012 7:31 AM
    • Marked as answer by Eileen Zhao Monday, March 05, 2012 7:25 AM
    Tuesday, February 28, 2012 4:52 PM

All replies

  • Have a look at htis http://tinyurl.com/87g89zw

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    • Proposed as answer by Koen Verbeeck Tuesday, February 28, 2012 6:16 AM
    Tuesday, February 28, 2012 5:17 AM
  • Hi,

    create three dataflow tasks one for each sheet. Please see the below link for dataflow task.

    http://cavemansblog.wordpress.com/2009/04/17/ssis-export-data-from-sql-server-2005-to-excel/

    Srikrishna

    Tuesday, February 28, 2012 5:23 AM
  • fist you have to make a SampleExcelFile.xls wuth 3 sheets and within each sheet it will have all the related Columns(fields)

    In your Pkg you need.

    1- you meed to COPY the file from one folder to another, so you will still have the copy of the sample file, ( you can rename it as well)

    2- make 3 DFT as "Srikrishna" had mentioned, within in each DFT set the source to the SQL TABLE and te destination to the excel

    3- My experiance is that i had lost of confict while inserting into multiple sheet in the same file, to by pass that i just made a CONSTRAIN (green Line) from DFT1 ----> DFT 2 ----> DFT3 , in this case i never had a problem.


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Tuesday, February 28, 2012 1:38 PM
  • is it not possible to load differnt  table structure into each excel sheet
    Tuesday, February 28, 2012 3:14 PM
  • yes its is possible ,after first sheet you have to use a execute sql task to create the another sheet in the same excel file , do the same for third dataflow task to

    in the execute sql task select the connection type to excel , and select the same connection manager that you used for first DFT

    use this link it is not exactly as what you are looking for but may solve your problem

    http://sqljunkieshare.com/2012/02/28/how-to-create-and-map-excel-destination-dynamically-in-ssis/

    Tuesday, February 28, 2012 4:08 PM
  • If you have 3 fixed sheets that needs to be populated from 3 different SQL table, i would make a SMAPLEEXCELFIEL.xls insted of making the sheets on the fly.

    if you make it on the fly , you have more work to do, and you need to know SSIS a little bit more, pluse once you make it on the fly please check the excel fields data type , sometimes it doesn't display what you want.

    good luck


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Proposed as answer by Eileen Zhao Wednesday, February 29, 2012 7:31 AM
    • Marked as answer by Eileen Zhao Monday, March 05, 2012 7:25 AM
    Tuesday, February 28, 2012 4:52 PM
  • One last thing i forgot to say is that, mainly (my experiance) when ever we export data to a Excel file, the file will be used by managers , and they want to have pie charts and etc..., so i basically get the excel file from them and use it as a sample file, when ever they add a new sheet for charts and etc... all they have to to is to overwrite the current ExcelSampleFile.xls with the new one, generally SSIS is not a good tool to make pie charts and etc... but very good for data transfer to excel

    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).


    Wednesday, February 29, 2012 4:35 PM