如何使用Bot按时间间隔检查SSRS报表就绪状态并通知客户端

Great question! Building a bot to monitor SSRS report readiness on a schedule and alert clients when it’s ready is totally doable with the right tools and logic. Here’s a step-by-step approach I’ve used in similar scenarios:

First, choose tools that integrate smoothly with SSRS and support scheduling + notifications. My top picks are:

• Power Automate (Microsoft Flow): Perfect if you’re already in the Microsoft ecosystem—no coding needed, built-in SSRS connectors, and easy notification options (Teams, Outlook, etc.).
• Python + Schedule Library: Great for custom logic; works well with SSRS’s REST/SOAP APIs and lets you send notifications via email, Slack, or webhooks.
• Azure Functions: Serverless option with timer triggers, ideal if you need scalability and don’t want to run a 24/7 script.

SSRS exposes APIs to verify report status—here’s the key breakdown:

• If you’re running the report on demand: Trigger the report execution first via API, then poll the execution status using the returned ExecutionID. A status of rsExecutionCompleted means it’s ready to view.
• If you’re monitoring a scheduled subscription: Fetch the subscription details and check the LastRunTime + Status field. A Success status and recent LastRunTime confirms the report snapshot is ready.

For example, using SSRS’s SOAP API, you’d call GetExecutionInfo() to pull status. With the newer REST API (SSRS 2016+), you can hit the /api/v2.0/reports({reportId})/Executions endpoint.

Example with Power Automate #

1. Add a Recurrence trigger, set it to run every 3 minutes.
2. Add an HTTP action to call the SSRS API (use NTLM auth if your SSRS uses Windows authentication).
3. Parse the API response with a Parse JSON action to extract status data.
4. Add a Condition to check if the status equals Completed (or your specific readiness criteria).
5. If the condition is met: Add a Post message in Teams or Send email (V2) action to notify your client.

Example with Python #

Here’s a quick script that checks every 3 minutes and sends an email alert when the report is ready:

import schedule
import time
import requests
from requests_ntlm import HttpNtlmAuth
import smtplib
from email.mime.text import MIMEText

# SSRS configuration
SSRS_URL = "http://your-ssrs-server/ReportServer/api/v2.0/reports('your-report-id')/Executions"
SSRS_USER = "domain\\username"
SSRS_PASS = "your-password"

# Email notification config
SMTP_SERVER = "smtp.yourdomain.com"
SMTP_PORT = 587
EMAIL_FROM = "ssrs-bot@yourdomain.com"
EMAIL_TO = "client@theirdomain.com"

def check_report_readiness():
    try:
        # Fetch latest execution status from SSRS API
        response = requests.get(SSRS_URL, auth=HttpNtlmAuth(SSRS_USER, SSRS_PASS))
        response.raise_for_status()
        executions = response.json()
        
        # Verify if the most recent execution is completed
        if executions and executions[0]["Status"] == "Completed":
            send_notification()
            print("Report is ready—notification sent!")
            # Stop scheduling after alert (optional)
            schedule.clear()
        else:
            print("Report not ready yet. Checking again in 3 minutes.")
    except Exception as e:
        print(f"Error checking report status: {str(e)}")

def send_notification():
    msg = MIMEText("Your requested SSRS report is now ready to view!")
    msg["Subject"] = "SSRS Report Ready Alert"
    msg["From"] = EMAIL_FROM
    msg["To"] = EMAIL_TO
    
    with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
        server.starttls()
        server.login(EMAIL_FROM, "email-account-password")
        server.send_message(msg)

# Schedule check every 3 minutes
schedule.every(3).minutes.do(check_report_readiness)

# Run the scheduler indefinitely
while True:
    schedule.run_pending()
    time.sleep(1)

• Authentication: Most SSRS instances use Windows auth, so ensure your tool supports NTLM (Power Automate does this natively; Python uses the requests_ntlm library).
• Avoid Overloading SSRS: Don’t set the interval too short—3 minutes is reasonable, but adjust based on how long your reports typically take to run.
• Stop After Notification: Add logic to halt checks once the report is ready (like schedule.clear() in the Python example) to avoid unnecessary API calls.
• Error Handling: Add retries or failure alerts so you know if the bot can’t reach SSRS or encounters an issue.

内容的提问来源于stack exchange，提问作者saini