Save Query Results to Excel and Automatically run it

Question

Save Query Results to Excel and Automatically run it

anonymous

SSC Enthusiast

Points: 174
More actions
September 30, 2022 at 4:29 pm

#4094073
Hello,

I have a very simple query
```
Select * from Employees 
where company = 'Company A'
order by firstName
```
I want to run this query to run automatically on a daily basis and save the results in excel. Is their any way I can achieve this purpose? I tried using SQL server jobs but they don't save the output in excel (its only txt file) and I couldn't understand SSIS packages. The query seems to run fine but when I put it in a package ( using export data option from sql server management studio) and run the package manually, it gives several data conversion errors.

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

One way to do it is to create a stored procedure that returns just the rows and columns you want, and then call that from Excel.

you just specify the source as SQL Server, set the credentials, and then if you click on the arrow to the left of Advanced Options, you can specify a stored procedure to run, for example

EXEC MySchema.MySprocName @Param1='text value', @Param2 = '10-Sep-2022'

and then the data will be imported into your spreadsheet.

anonymous SSC Enthusiast Points: 174 More actions · Answer 2

Actually, I wanted the excel sheet to be created automatically, and the query to run automatically too.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Then you could do something like create a stored procedure to get the data you want, then use SSIS to export it to an excel file, then schedule it to run daily.

anonymous SSC Enthusiast Points: 174 More actions · Answer 4

Could you share how to create SSIS and schedule it. Actually, there are so many steps on the internet and I tried several of them but in the end, I ended up confused with this process.