SSRS Export to Excel in different excel files

Question

SSRS Export to Excel in different excel files

rasikawtc

Old Hand

Points: 312
More actions
December 8, 2011 at 10:22 am

#249386

Hi
Is there a way to split a huge excel file into smaller files while exporting
for example if there are 10,0000 rows in the report when exported into excel into should create 10 different .xls or CSV files each file having 10,000 rows.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

rasikawtc (12/8/2011)
Hi
Is there a way to split a huge excel file into smaller files while exporting
for example if there are 10,0000 rows in the report when exported into excel into should create 10 different .xls or CSV files each file having 10,000 rows.
Thanks

Not as far as I know. The only thing you can do is to force a page break, which would then cause the data to be split to different worksheets...but still within the same Excel file.

Page breaks will not have any effect when exporting to CSV's.

If you want to do something like this, you should rather consider using SSIS for that purpose.

Martin.

rasikawtc Old Hand Points: 312 More actions · Answer 2

Thanks Martin...but can someone please give some more information on how to do it in SSIS as I am not familiar with it .

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

rasikawtc (12/8/2011)
Thanks Martin...but can someone please give some more information on how to do it in SSIS as I am not familiar with it .

Books Online is your friend in this case. You will have to do some research...

Just to be clear though...you will not be able to invoke an SSIS package when exporting data from a report in Reporting Services. There may be a round-about way to do that, but I wouldn't recommend it.

I guess the point I was trying to make is that SSIS is the right tool when you want to split things into separate Excel files etc. Depending on the report, you may also be able to create subscriptions with different parameters, and save the output as Excel.

As you can see, there are many options but it all depends on the business case and requirements. Why somebody would want to run and export such a large report is questionable to say the least...nobody is going to look at that much data.

Martin.