locked
Predefined excel report sheet RRS feed

  • Question

  • Hi,

    I am trying to export data from DB to excel sheet. I have to use a predefined excel sheet.

    My questions here are

    1. How do i use a predefined excel sheet. That predefiend excel sheet has some formats my data should be exactly placed in that location only. Is it possible to achieve. If yes how can i do that?

    2. I want to place the row count on the top of the excel . say in row 1 i need count, name from DB should be in row 2, address in row 3. How can i achieve this. Pls throw some light on this.

    Thanks and Regards,

    Dumbi dooooo

    Thursday, September 9, 2010 10:30 AM

Answers

  • what i do is i have a empty template predefine excel sheet , with all the nice colors , right headers and maybe charts and i save it in a folder, when the Package runs it copies the empty excel file to a folder (in your case maybe called ToBeEmailed folder) and the next step in the package is a DFT that populates the excel file with data (main part of the ETL) , and finally after the DFT i attache the file to an email and email it.

    in my case i copy the file to a backup folder to keep track of what i have done.

    Q : Placing a Row count ....

    A: you can do this in the DFT see the what ETL vs ELT has suggested or look at something similler at http://support.microsoft.com/kb/908460

    Q: say in row 1 i need count, name from DB should be in row 2, address in row 3. How can i achieve this. Pls throw some light on this

    A: best suggestion is to make multiple Sheets and save each type of data in seperate sheet , and finally use a predefined sheet that is retreving data from the other sheets and displaying what you want. why having multiple sheets , well its easier to use in SSIS insted of inserting into a cell number and go through all those hard coding and testing in SSIS , all i am saying is that if you are going to have a predefined templat excel file , make it easier and let the excel file do some work for you , dont do evert thing in SSIS ,

    just to show you how hard and time consuming it to set SSIS to point to a celll and etc.... check the links, let excel do some work for you....THEY ARE NOT YOUR ANSWERS

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/224fa21d-71e5-441a-aa75-2dafa1a148eb/#6f741bf7-f771-4172-82db-2b519196d119

     

     

     


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, September 9, 2010 3:08 PM

All replies

  • for 2nd question, you have to go with script component as destination.

     

     


    Let us TRY this |

    My Blog :: http://quest4gen.blogspot.com/
    Thursday, September 9, 2010 11:08 AM
  • hi i am intended to use only a predefined excel as it has to be sent in mail as attachment. Can you pls tell me how can i use that predefined excel as my destination??

    Thursday, September 9, 2010 11:22 AM
  • what i do is i have a empty template predefine excel sheet , with all the nice colors , right headers and maybe charts and i save it in a folder, when the Package runs it copies the empty excel file to a folder (in your case maybe called ToBeEmailed folder) and the next step in the package is a DFT that populates the excel file with data (main part of the ETL) , and finally after the DFT i attache the file to an email and email it.

    in my case i copy the file to a backup folder to keep track of what i have done.

    Q : Placing a Row count ....

    A: you can do this in the DFT see the what ETL vs ELT has suggested or look at something similler at http://support.microsoft.com/kb/908460

    Q: say in row 1 i need count, name from DB should be in row 2, address in row 3. How can i achieve this. Pls throw some light on this

    A: best suggestion is to make multiple Sheets and save each type of data in seperate sheet , and finally use a predefined sheet that is retreving data from the other sheets and displaying what you want. why having multiple sheets , well its easier to use in SSIS insted of inserting into a cell number and go through all those hard coding and testing in SSIS , all i am saying is that if you are going to have a predefined templat excel file , make it easier and let the excel file do some work for you , dont do evert thing in SSIS ,

    just to show you how hard and time consuming it to set SSIS to point to a celll and etc.... check the links, let excel do some work for you....THEY ARE NOT YOUR ANSWERS

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/224fa21d-71e5-441a-aa75-2dafa1a148eb/#6f741bf7-f771-4172-82db-2b519196d119

     

     

     


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, September 9, 2010 3:08 PM