I have created dozens of SQL jobs that simply build a report from a query, then email it in HTML format. (Just T-SQL, not using SSRS).
Now I have a request to save/push that to a file folder. (I don't know if it's possible because I'm not currently generating a "file". )
Is this possible to do?
Thanks.
What have you found so far that is this is possible or not?
This example looks somewhat promising:
bcp "exec northwind.dbo.usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM dbo.Products'" queryout report.html -c -T -S PCN70152\SQL2K14
I doubt you want to email them an html file. so why are you opting to send them a file instead of an email. Is this related to your broken SMTP issue?
I would recommend you Fully think it through
Correct. I just want to store it in a file folder. No emailing for this one.
Why would you save this file? What is the requirement, what are you trying to solve
No, not related to the broken SMTP which has mysteriously "fixed" itself.
I'm already emailing the report in HTML format. Works beautifully. But we have several employees who do not yet have email - so managers have requested that, in addition to emailing the report - to also save it to a file folder. Ugh long story.
I am not a fan of such approach coming down from managers. Hence the beauty of SSRS.
Emailed reports or reports dropped in a file become stale the minute they are sent unless it is understood by all it is a snapshot in time.
After the SSRS report not being sent for 10 days, I'm no longer a fan of SSRS 
But I realize that was likely an SMTP issue. long-term, I'll get these reports migrated to that solution.
Otherwise you will have too many processes
with SSRS (and some auditing, notification of failed emails etc) you can accomplish both
I prevent all users from downloading anything, wastes paper and avoids excel file hoarding, that are useless.
I have to agree with @yosiasz - it seems you are spending an inordinate amount of time and effort to avoid using the built-in functionality of SSRS and/or SSIS.
I prefer using SSRS subscriptions to generate Excel/PDF/Word files and SSIS for text based file types (csv, pipe, txt, etc.). Either can be used to solve the issue you are having - and avoiding using those because there was an SMTP issue isn't reason enough in my opinion.
Now - if you didn't already have either SSRS or SSIS I would recommend looking at a completely different approach. If the idea is to create a file and send that file to certain recipients - and save the file for history/other access - I would create a powershell script to do the work.
I agree, @jeffw8713 . I'm going to request a new server to be purposed for SSRS. We also need to start using SSIS for file imports. Should SSIS be used on the database server or would it be better to install it on the SSRS server?
I am confused now - why would you need yet another SSRS server? You already have more than one available - and since SSRS can connect to any ODBC data source there should be no reason to create a separate instance just for one report - or for a set of reports for a single application/instance.
Just to be clear - I will use agent jobs to generate reports, but those are generally a simple delimited file to be attached or I embed an HTML table in the email message. As soon as the requirement becomes something more than that - it gets moved to SSRS or SSIS depending on the new requirements.
I will use SSRS when the output must be an actual Excel/PDF/Word document - since those are built-in to the product and it is just easier to generate. However, if the requirement is to create those files with date/time stamped file names - then it gets moved to SSIS where I have more control over how the files are created.
If the requirement is to just be able to show the users prior versions - and they don't have a specific requirement for a file or email, then I implement it in SSRS, generate the report as a snapshot on a schedule and save the snapshots for a set amount of time.
Thanks @jeffw8713
The only use of SSRS we have is one report on a Dev server. I built that as a prototype of sorts. Now, as we have additional requests for SSRS reports, it's time to build a true reporting server.
Because we will likely have later requests for reports that are better suited for SSIS..... is it appropriate to use SSIS on that same reporting server? Keep in mind this is a very small company with limited resources.
This is the confusing part - you stated here that the report wasn't sent for 10 days. That implies you have an instance of SSRS and one or more reports deployed, which is why I recommended using SSRS. That gives you the best options - the ability to display the reports, create snapshots to maintain historical reports, subscriptions to output to files or email the reports, etc.
With that said - it is going to be quite expensive to build out a production Enterprise Edition that can support both SSRS and SSIS. It really depends on the load you are going to place on that system - which will determine how many CPUs are needed and how much memory, as well as how much disk space you need for any local databases on that instance.
You may be able to get away with 4 CPUs and 8GB of memory - allocating 2GB to SQL Server, which would be the absolute minimum configuration I would support. However, if either your SSIS or SSRS processing needs more resources - it will require more licenses and Enterprise licenses are quite expensive.
For just a few processes - it probably would not be worth the cost. You are going to have to determine that - I have no idea how many of these types of processes you are going to end up creating, nor can I figure out whether it is cost effective or not.
Based on this - I would recommend that you spend some time learning Powershell and scripting. Everything you are doing now in SQL for the HTML emails can be done in PS - as well as having the ability to create the output as files. You can easily perform the data-driven email requirements by running that query into a variable in PS - looping over each item in the results - and use PS to build/create the email messages and files.
That would be a much more cost-effective solution - but does require more knowledge and probably quite a bit more development effort to manage and maintain that type of solution. The benefits of SSRS and SSIS would be in the reduction of time spent on development vs the cost of the solutions.
So you were sending these emails from a dev server? 
It was done only as a prototype. Now I'm ready to move it to a production server and develop more SSRS solutions there as well.
@jeffw8713 If i install (2019) SSRS onto my database server, will I need to buy licenses? The instance is 2019 Standard.
I'm confused because I read this on another site: "SSRS is still part of the SQL Server technology stack and part of the license.
Version 2017 SSRS comes with a separate installer, where you can use your SQL Server product key to install SSRS 2019."