NB Relates to SQL Server 2005
I need to generate a daily report that generates a TXT file of the data required and saves it to the file system (actually an SFTP location as well). I can generate this file by pasting in the SQL command and selecting "Results to File".
However, what I would now like to do is to automate this daily task. As I understand it, I can do this with an SQL Server Agent. However, it looks like I can only paste in the SQL, but not do a "results to file" type option. I therefore think I need to create a SIS (SQL Server Integration Services) Package as the step type in the job. This is where I've got a bit stuck. As I understand, to create a SIS Package, you need to do this from SQL Server Business Intelligence Studio ... but when I open it I am prompted to create one of the following:
Analysis Services Project
Integration Services Project
Import Analysis Services 9.0 Database
Report Server Project Wizard
Report Server Project
Could you let me know which one to use and how to go about creating the correct SIS package (assuming I am on the right lines).