Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How Populate Multiple Result sets to Diffrents Sheets in One Excel Workbook Dynamically through Stored Procedure in SSIS

Answered How Populate Multiple Result sets to Diffrents Sheets in One Excel Workbook Dynamically through Stored Procedure in SSIS

  • Sunday, March 04, 2012 8:57 AM
     
     
    Sign In to Vote
    0

      Hi All,

    I Need to Populate  Multiple Result sets to Diffrents Sheets in One Excel Workbook Dynamically through Stored Procedure In SSIS

    Ex: 

    create proc multipleresultset(@empid int)
     as begin
     select * from HumanResources.Employee e where e.EmployeeID=@empid
     select * from HumanResources.EmployeeAddress ea where ea.EmployeeID=@empid
     end  ...

    please share your knowledge ASAP . 


     

All Replies

  • Sunday, March 04, 2012 12:10 PM
     
     
    Sign In to Vote
    0
    Hmm, I think  you need to use UNION ALL to build one result set (put NULL to make number of columns the same ) and insert it into EXCEL file

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • Sunday, March 04, 2012 12:38 PM
     
     
    Sign In to Vote
    0

     Thanks Uri Dimant for your reply. But in this case we can't apply UNION ALL because two table are different(means no.of colums and datatypes are diffrents). 

    what i actually need is getting multiple resultsets from diffrent tables in one stored procedure. i have to call the stored procedure in Execute Sql Task and has to populate the every dataset to one sheet in excel file.


       I am Waiting for Your Reply . Thank you

     
  • Sunday, March 04, 2012 9:02 PM
    Moderator
     
     
    Sign In to Vote
    0

    when you read data from SP in SSIS just the first result set will be fetched nothing more

    so if you want to use multiple result sets, you can do it in these ways;

    1- use different SPs for each result set

    2- use custom scripting and read results from SP programmatically and use them

    http://www.rad.pasfu.com

     
  • Sunday, March 04, 2012 11:59 PM
     
     Answered
    Sign In to Vote
    0

    make a template of a excel file ( lets call it ExcelSample.xls) and make 2 sheets within it , add the column and etc.. for each sheet .

    make 2 SP

    first one

    create proc multipleresultsetFIRST(@empid int)
     as begin
     select * from HumanResources.EmployeeAddress ea where ea.EmployeeID=@empid
     end  ...

    Second one

    create proc multipleresultsetSECOND(@empid int)
     as begin
     select * from HumanResources.Employee e where e.EmployeeID=@empid
      end  ...

    in this like the package is dealling with one sheet you can go and add another DFT to make your second sheet in the package , what the package does is that , it gets data from sql loads it into an excel file and emails it to the user

    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).