We’re running into a frustrating issue with our financial reporting system. After our nightly ETL jobs complete successfully around 3 AM, the ad-hoc reports in SSRS still show yesterday’s data until we manually refresh the data sources. The ETL jobs are scheduled through SQL Agent and complete without errors, but there’s clearly a caching problem somewhere.
I’ve checked the SSRS data source caching settings, and they’re configured with a 24-hour cache expiration. The ETL job timing seems fine - it finishes by 3:15 AM and our first reports run at 7 AM. But users are complaining that morning reports show stale data.
Has anyone dealt with automated cache invalidation after ETL processes? We need these reports to reflect fresh data automatically without manual intervention each morning.
SELECT DataSourceName, CacheExpiration
FROM ReportServer.dbo.DataSource
WHERE Path LIKE '/Finance/%'
Instead of fighting with cache invalidation, consider adjusting your caching strategy. Set your data sources to use shorter cache windows (like 4 hours) or implement query-based caching that checks for data freshness. You could also add a timestamp column to your fact tables that the reports check - if the timestamp is recent, use cached data; if it’s old, force a refresh. This gives you more control without relying on API calls.
We had the same problem with our warehouse reports. What worked for us was adding a final step to the SQL Agent job that calls the SSRS API to flush the cache. You can use rs.exe utility or PowerShell to trigger this. The key is making sure your ETL job timing includes this cache invalidation step as the last operation before marking the job complete.
We solved this by creating a stored procedure that SSRS calls to check if new ETL data is available. The procedure compares the last ETL run timestamp against the cached data timestamp. If ETL is newer, it returns a flag that triggers the report to bypass cache. Simple and effective without changing your ETL infrastructure.