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

    Question

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


    Sunday, March 04, 2012 8:57 AM

Answers

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


    Sunday, March 04, 2012 11:59 PM

All replies

  • 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:10 PM
  •  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 12:38 PM
  • 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 9:02 PM
    Moderator
  • 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).


    Sunday, March 04, 2012 11:59 PM