Running Batch SSRS (SQL Server) Reporting Services Reports using Powershell

We recently needed to run a one page report 10,000 times. Using previous methods of nesting reports using Report Builder and SSRS didn’t work. We encountered timeouts and memory issues on the server. We could have spent time changing timeout values and figuring out how to overcome this using SSRS. But we found an easier way….

Previously I had written a small C# application using the Microsoft Report Viewer Runtime (a winforms component) to run a batch of reports, create a folder structure and output to PDFs in the respective folders. I decided to re-use this solution, but tailor it to the new reports that we needed.

I encountered an issue with my visual studio and needed to get this batch report sorted, so we looked into using powershell. Below is an anonomised version of the script we used.

It uses the Microsoft Report Viewer Runtime 2012 (dll is v10) to call the report and export to PDF. Using Powershell to interact with .NET is easy and allows you to leverage the short hand coding nature of powershell with the wide array of easy to use libraries that .NET provides.

## Reference ReportViewer Library
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
Version=10.0.0.0, Culture=neutral, `
PublicKeyToken=b03f5f7f11d50a3a")</code>

## Report viewer object
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

## Set Report Processing to server mode
$rv.ProcessingMode = "Remote"

## Set the report server base url
$rv.ServerReport.ReportServerUrl = "http://servername/reportserver"

## Set the report path
$rv.ServerReport.ReportPath = "/base folder/folder/ReportName"

## Create an array with all the required ids
$ids = (0..10000)

## Loop through all the ids
foreach ($id in $ids)
{
# Parameters array
$params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 1
$params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("ParameterName", $id, $false)

# Set Report Parameters
$rv.ServerReport.SetParameters($params)

# Refresh / Run Report
$rv.RefreshReport()

# Out vars
$mimeType = $null
$encoding = $null
$extension = $null
$streamids = $null
$warnings = $null

# PDF file generation
$bytes = $rv.ServerReport.Render("PDF", $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);

## Output to file
$file = "C:\FolderName\$id.pdf";

$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);


$fileStream.Close();
}
Advertisements

2 thoughts on “Running Batch SSRS (SQL Server) Reporting Services Reports using Powershell”

  1. I am a Powershell noob. How exactly do I use the above to get the results I need? I am looking to run an SSRS report that will produce thousands of records each having a unique ID. The result should be that a new PDF will be generated when a different unique ID is identified.

    Thanks!

    Like

    1. Hi Conor, if you are new to scripting and development you may struggle with this. It’s quite advanced and use’s .NET Structures to access the report server.

      In general you are going to need a way of representing your unique ID’s in powershell, mine was simple – I used an array (line 19) and filled it with the numbers 0 -> 10000. Then you will iterate through the ID’s, passing the the report viewer control one ID at a time. This is what the foreach structure (line 22) does.

      The tricky bit for me was passing in an array of parameters, even though my report only required one parameter.

      Good luck, i hope this helps.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s