This is definitely possible!
I'll blog about this in much more detail, but here is a high-level overview. Let me know if this is possible for you to do!
Basically, you are having SSRS email an attached report to a shared mailbox. Then Power Automate monitors this mailbox, takes the attachment from the email and then saves it to a SharePoint document library.
1. Get a static IP from your ISP. It is worth it in the long run anyway to connect lots of GP things to MS365 (Power BI, Teams, SharePoint, Power Apps).
2. In the Exchange admin center on MS365, go to Mail Flow > Connectors. Set up a connector that is from Your Org to O365. You'll fill in the static IP here.
2. Set up SMTP in your SSRS. You'll use the SMTP server that you get from MS365 -- the domain-com.mail.protection.outlook.com one.
3. Create a shared mailbox in MS365. Make sure to give the owner of the Flow you're going to create in Power Automate access to it.
4. Set up the subscription in SSRS to email the report to this shared mailbox.
5. In Power Automate, you're going to create a new Flow that monitors this shared mailbox, takes the attachment from the email and saves it to the document library of your choice.
Will have all the details on my blog in a bit! https://josephmarkovich.com.
Once you set this up, you'll also no longer need to fight with MFA and GP. Everything just gets sent through your organization's SMTP server from Microsoft.