Save Query Results to Excel and Automatically run it

  • 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.

  • 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.

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

  • 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.

  • 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.

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

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