I have stored procedure with three parameters and end result is in a temp table.
and last statement of the stored procedure is Select of the #temp table. Now I have requirement to send temp table data to Excel file.
I planning to use SSIS package so it can be scheduled to run.
I need help to pass the Stored procedure result data into excel file.
thanks
when you say excel, are you talking about a csv file or xlsx? Alot of people equate csv file with excel, but that is wrong.
If the file must be in Excel format (e.g. xlsx file) then SSIS would be the method I would recommend. However, you are going to run into some issues that will need to be addressed.
You need the appropriate ACE drivers installed - both the x86 and x64 drivers. The x86 driver is needed for development and the x64 driver will be needed for the runtime. To install the version that is not currently installed on the server/workstation - run the installation from the command line with either the /quiet or /passive qualifier.
You will need a 'template' Excel file that already has the expected Sheet and columns defined. As part of the process you will copy this template to your working file - update the Excel connection to use this new file (or the working file can be a fixed name/path that you later copy/rename) as the destination.
Create a data flow that uses an OLEDB Source - and the Excel flat file destination. You may also need a derived column transformation to modify column data into the appropriate data type for Excel.
Remove the temp table from the stored procedure - SSIS will have an issue with the temp table. Instead of inserting everything into a temp table and then selecting - just select for the stored procedure output. If that is not possible...then you need to modify the call to use WITH RESULTSET to define the columns being returned.
You can greatly simplify the whole process if you output a CSV file instead. That file can still be opened in Excel and the end users can apply whatever filters/formatting they want and save as a true Excel file.
One final note - if you can use an CSV file you would not need SSIS to accomplish this...it can all be done in T-SQL and sent to the user using sp_send_dbmail. This would also allow for using the temp table as is...if interested in that approach I would need an example/sample of your current code.