April 28, 2011 at 2:04 pm
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!
April 29, 2011 at 1:46 am
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.
April 29, 2011 at 6:50 am
What im trying to do is copy all the 3 resultsets to one excel spreadsheet. So all three will be on one excel tab.
April 29, 2011 at 9:36 am
Are the three result set identical as far as columns? Can you UNION them together?
April 29, 2011 at 9:38 am
Oh I'm sorry you answered that question. Are the three sections side by side or underneath each other?
April 29, 2011 at 11:19 am
The 3 sections are underneath each other. So the final output is 3 sections one under the other separated by an exrta row.
April 29, 2011 at 11:27 am
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.
HTH.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
April 29, 2011 at 1:31 pm
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.
Alternate1-
http://support.microsoft.com/kb/219151
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.
June 9, 2011 at 4:01 pm
Hello, were you able to fix this issue? I am also facing the same issue.
June 9, 2011 at 4:29 pm
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.
June 9, 2011 at 4:33 pm
Do you have any idea, can we call three SP in one package to generate 3 DIFFERENT files?>
December 26, 2016 at 11:31 pm
Did you got the answer? My question is also same one?
December 28, 2016 at 7:17 am
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.
December 30, 2016 at 6:56 am
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