SSIS - Export multiple SQL queries to one excel spreadsheet

  • Hi All,

    I am using SSIS and I am trying to export 3 separate SQL queries to one excel spreadsheet. These queries each have different number of fields and different metadata.

    I need to export them to the excel sheet and have 3 sections with the resultsets from the queries.

    Anyone know how to do this using SSIS? Any help appreciated!

  • I assume you mean one resultset to each worksheet?

    If so, you can set up the Excel destination to explicitly copy to a named worksheet; you would need three Excel destinations

    If data can screw you, it will; never assume the data are correct.

  • What im trying to do is copy all the 3 resultsets to one excel spreadsheet. So all three will be on one excel tab.

  • Are the three result set identical as far as columns? Can you UNION them together?

  • Oh I'm sorry you answered that question. Are the three sections side by side or underneath each other?

  • The 3 sections are underneath each other. So the final output is 3 sections one under the other separated by an exrta row.

  • Can't help but think you're making things difficult for yourself.

    Doing everything in a single tab would need some programming to work out where the first set of data ends and the second should begin.

    As you have three separate (non-union) outputs I'd argue that each could go on a separate tab as the results can be easily viewed this way.

    Would the business accept a spreadsheet containing three tabs?

    Admittedly I write the above without knowing any detail about your particular circumstances.


    If data can screw you, it will; never assume the data are correct.

  • Here is a kludge possible solution if you really want it.

    Create a a master generic table with enough columns to accommodate your widest result set. They'll probably need to be all character datatypes since you are mixing columns.

    Insert your datasets, convert you numbers as necessary. Insert 2 blank lines. Insert column headings. Put a 'sort' column in your table and populate as you are inserting so you can control the sort of your final output.

    Export the master table. You may have numbers in character columns but if this is a 'report' then that may be acceptable.


    Alternate2-Have the Excel spreadsheet call the data from SQL server. Then you can control the location of the data.

    Trying to automate a historical manual excel workflow is never fun. If you can convince the powers that be to accept an alternate format, all the better.

  • Hello, were you able to fix this issue? I am also facing the same issue.

  • When I used to build this sort of thing I would create a "data" tab that connected to the SQL server and pulled down the dataset. each one on its own excel tab. Then I would set up a "main" tab with pretty formatting and dynamic links to the datasets for presentation. Hide the "data" tabs so the end user doesn't know they are there. All they have to do is hit the refresh button on the toolbar and viola fresh report.

  • Do you have any idea, can we call three SP in one package to generate 3 DIFFERENT files?>

  • Did you got the answer? My question is also same one?

  • Do you have any idea, can we call three SP in one package to generate 3 DIFFERENT files?>

    Yes, you can. You will need three different Excel connections to do this.

  • I agree with this solution, by building a master table or a Temp table, throwing all your data in their first, than exporting to Excel. much simpler.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply