Note: Do not attempt to select more than 1 million rows into the resulting Excel Spreadsheet or the data selection process may run out of memory.
Large reports present certain processing challenges and require certain configurations if they are to run properly. Large reports should not be run on demand unless they are configured to support pagination.
1) There is not much you can do in order to overcome the System Out of Memory issue except to get more memory.
2) Excel 2003 has a row limit of just over 65,536 rows, and Excel 2007 has a limit of 1,048,576. So if you mean 2.5 million you won’t be able to export to Excel, even if you are using the 2007 version. The should be no limit to saving to .csv as it is essentially a text file.
Only way to describe it is that in SSRS 2008 there is no limit on the number of rows your report can consume.
That being said, often we find that when the number of rows reaches over 1 million, customers are trying to use SSRS as an quick and dirty ETL tool. We suggest SQL Server Integration Services to help with making these scenarios better.
Before you configure report distribution, it is important to know which rendering clients can accommodate large documents. The recommended format is the default HTML rendering extension with soft page breaks, but you can choose from any format that supports pagination.
Performance and memory consumption varies for each rendering format. The same report will render at different rates and require different amounts of memory depending on the format you select. The fastest and least memory intensive formats include CSV, XML, and HTML. PDF and Excel have the slowest performance, but for different reasons. PDF is CPU-intensive, while Excel is RAM-intensive. Image rendering falls in-between the two groups. You can specify the format when you define how the report is distributed.