Automated KPI Reporting Pipeline

// SSIS Script Task – Row Count & NULL QA Check
// Runs after each source extract
public void Main()
{
    string table = Dts.Variables["User::StagingTable"].Value.ToString();
    int rowCount, nullCount, expectedMin;

    using (var conn = new SqlConnection(connStr))
    {
        conn.Open();
        // Check row count
        rowCount = (int)new SqlCommand(
            $"SELECT COUNT(*) FROM stg.{table}", conn)
            .ExecuteScalar();

        expectedMin = (int)Dts.Variables["User::ExpectedMinRows"].Value;

        if (rowCount < expectedMin)
        {
            Dts.Events.FireError(0, "QA Check",
                $"Row count {rowCount} below minimum {expectedMin}",
                "", 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
            return;
        }

        // Check NULL rate on key column
        nullCount = (int)new SqlCommand(
            $"SELECT COUNT(*) FROM stg.{table} WHERE key_id IS NULL",
            conn).ExecuteScalar();

        if ((nullCount * 1.0 / rowCount) > 0.01) // >1% NULLs
        {
            Dts.Events.FireWarning(0, "QA Check",
                $"NULL rate {nullCount/rowCount:P} exceeds threshold",
                "", 0);
        }
    }

    // Log to pipeline_log table
    LogPipelineRun(table, rowCount, nullCount);
    Dts.TaskResult = (int)ScriptResults.Success;
}

// Power Automate Flow Pseudo-logic
// Trigger: "When a Power BI dataset refresh completes"
//   Group: KPI_Reports_Workspace
//   Dataset: Daily_Operations_Model

TRIGGER: Power BI Dataset Refresh Succeeded

ACTION 1: Get items from SharePoint list 'ReportRecipients'
  // Columns: Name, Email, Role, ReportPageName

ACTION 2: Apply to each item in ReportRecipients

    ACTION 2a: Export Power BI report as PDF
        WorkspaceId : "KPI_Reports_Workspace"
        ReportId    : "Daily_Ops_Report"
        Pages       : item[ReportPageName]   // role-specific page
        FileFormat  : PDF

    ACTION 2b: Send an email (Outlook)
        To      : item[Email]
        Subject : "📊 Daily KPI Report – @{formatDateTime(utcNow(),'dd MMM yyyy')}"
        Body    : "Hi @{item[Name]}, please find your daily KPI report attached."
        Attach  : PDF from Action 2a

ACTION 3: Condition any anomaly_flag = 1 in SQL table?
    IF YES: Post message to Teams channel "⚠️ Data anomaly detected in today's KPIs"
    IF NO:  Terminate successfully