Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?
We had a similar issue with a query that was called from EntityFramework.
Was fast in SSMS, but slow from application.
It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable.
After fixing this issue, the query was fast from application.