Automated Batch Export of SSRS Reports via PowerShell

Automated Batch Export of SSRS Reports via PowerShell & SQL Agent

Overview: This solution automates bulk document extraction and rendering from SQL Server Reporting Services (SSRS). It documents the transition between the legacy native subscription injection mechanism and the PowerShell queue-driven export pipeline. The environment utilizes dedicated SQL Server Agent jobs to orchestrate batch student performance graphs, handle parameter XML serialization, and render files to network shares.

Architecture & End-to-End Workflow

[ Crystal Reports / Application UI ]
              │
              ├─► Legacy: Writes to [dbo.uReportServerSubscriptionLog] 
              │            └─► SQL Agent: "Graphinator" executes dbo.uspgReportServerSubscription
              │                 └─► Calls ReportServer.dbo.CreateTask / CreateSubscription
              │
              └─► Modern: Writes to [dbo.uReportServerBatchQueue] (StatusCode = 0)
                           │
                           │ Polled via SQL Agent / Scheduled Script
                           ▼
                  [ Get-BatchReports.ps1 ]
                     │              │
                     │ LoadReport() │ SetExecutionParameters()
                     │ & Render()   │
                     ▼              ▼
           [ SSRS SOAP Web Service ] (http://localhost/ReportServer)
                           │
                           │ Returns binary stream
                           ▼
              [ File System Destination ] (e.g. \\Server\Share\ExportFolder\...)
                           │
                           │ Updates StatusCode = 2 (Success) or 3 (Failure)
                           ▼
              [ dbo.uReportServerBatchQueue ]
                           │
                           ▼
          [ SQL Agent: Rename / Post-Processing Jobs ]

1. Batch Request Ingestion: An application routine or Crystal Reports batch export identifies the target student cohort and inserts execution metadata into either the modern queue (dbo.uReportServerBatchQueue) or the legacy subscription log (dbo.uReportServerSubscriptionLog).
2. Queue Polling & Dispatch:
   • Modern Pipeline: A scheduled script or task invokes Get-BatchReports.ps1, retrieving all pending jobs where StatusCode = 0.
   • Legacy Pipeline: The SQL Server Agent job "The Graphinator" polls uReportServerSubscriptionLog for unprocessed records modified within the past 3 days (len(OwnerID) = 0) and processes them via dbo.uspgReportServerSubscription.
3. Web Service Proxy Initialisation: In the PowerShell pipeline, the script instantiates a SOAP client against the local SSRS execution endpoint using New-WebServiceProxy and Windows Integrated credentials (-UseDefaultCredential).
4. Session Instantiation & Parameter Binding: The report definition is loaded via LoadReport(), parameters are unpacked from XML, and then explicitly bound to the execution session using SetExecutionParameters().
5. Stream Rendering & File Creation: The SOAP Render() method exports the document to binary (such as PDF). After validating that the byte stream is non-zero, the array is written to disk via .NET file I/O.
6. Queue Reconciliation & Renaming: The database record is updated to StatusCode = 2 (Success) or StatusCode = 3 (Failure). Downstream jobs (such as Rename graph reports) standardize file naming conventions.

SQL Server Agent Jobs in Play

SQL Server Agent manages scheduling, triggering, and file handoffs across several specific jobs:

Database Objects in Play

Queue Table Schema (dbo.uReportServerBatchQueue)

• BatchSeq (int, PK): Unique batch row sequence.
• ReportPath (varchar): SSRS catalog path (e.g. SiteReports/PerformanceGraph).
• FileName (varchar): Destination file name.
• ExportFilePath (varchar): Local or UNC target directory path.
• ReportParameters (xml): Serialized parameters (e.g. <ParameterValues><ParameterValue><Name>ID</Name><Value>12345</Value></ParameterValue></ParameterValues>).
• StatusCode (int): Execution state (0 = Pending, 1 = In-progress, 2 = Success, 3 = Failure).
• LastStatusMsg (varchar): Status text or caught exception details.
• CreatedDate / ModifiedDate (datetime): Record audit timestamps.

Script Configuration Reference

Global execution options are isolated in C:\Scripts\graphreports\config.ps1:

Common Failure Modes & Troubleshooting

1. 0-Byte Destination Files
Root Cause: Writing to disk using [System.IO.FileMode]::Create before verifying that the SOAP render call returned bytes. If SSRS throws a parameter validation error or the session aborts, an empty file is written.
Resolution: Ensure commonCode.ps1 validates $null -ne $RenderOutput -and $RenderOutput.Length -gt 0 before opening the file stream, and wrap execution in a structured try/catch block.

2. HTTP 503: Service Unavailable
Root Cause: Setting $rsinstance to an invalid virtual directory (e.g. using the SQL instance name ReportServer_INSTANCE instead of ReportServer), or pointing $rsserver to an external FQDN blocked by Windows loopback security.
Resolution: Configure $rsserver = "localhost" and $rsinstance = "ReportServer". Verify availability in a local browser at http://localhost/ReportServer/ReportExecution2005.asmx?WSDL.

3. "Value provided for parameter 'X' is not valid" (rsInvalidReportParameter)
Root Cause: Cascading parameter validation failure. SSRS rejects child parameters (such as student ID) if the parent parameters (FileYear, FileSemester) are missing from the execution session, because the child value does not exist in the filtered "Available Values" query.
Resolution: Verify the ReportParameters XML column in dbo.uReportServerBatchQueue contains all required parent filter values.