Excel report should support 1million rows and 800 columns. The application has to generate excel in two modes 1.online, where on selecting an entity on grid view excel report has to be generated . 2.offline,user can select multiple entities and using a button on UI can trigger it to generate excel and can receive excel via mail.
Currently application supports the above functionality ,but the export is very slow .Application and Database is on the same server. Keeping that in mind we have to design a framework where it will be fast even incase of Data on the application is increased by 10x with minimal performance issues.
Does SSIS will serve the purpose or if any other please suggest?
1 million of records definitely will take some time and in a concurrent environment this is not good. It is best to have services oriented architecture, or just execute your code asynchronously to prevent this hanging. The processing of the export (for example in a service) should run in background threads to handle the load.
When completed generate and send mail as you mentioned. Or save in an application temp dir and use SignalR for example (or just pooling the server with Ajax) to check if the process in the service has completed. If yes, redirect the client to download the file.