SC Essentials: Schedule Reports with Powershell


If you are using System Center Essentials with a SQL Express Edition it becomes impossible to schedule reports through the SCE console or Reporting Services. However you can do it with powershell!

First think about a report you want to schedule. I’ll be using the Availability report.
Fill In all your Fields.


Run the report.


Now go to File => Save to management pack…
Saving your report in a management pack, saves you the burden of configuring all parameters later on in the script.

Open your browser and go to your reportingserver url. Here you will find a new folder (i.e. iscoolReports)


Click on it and you will see a random generated name (the middle one). You can click it to verify if the report is actually the one you need.
In the script we will use $reportPath it is here that you can find it. /iscoolReports/UICustomReport4dc3c6e9ca34440e9119fe3c2920f87a

Now we will make the Powershell script:

######################################################
###Report_MyCoolServerAvailabilityPrevMonth.ps1
###
######################################################

# The $reportServerURI says 2005 at the end, this is even true if you are using sql 2008
$reportServerURI = http://sce.iscool.local//ReportServer_Essentials//ReportExecution2005.asmx?wsdl
$RS = New-WebServiceProxy -Class ‘RS’ -NameSpace ‘RS’ -Uri $reportServerURI -UseDefaultCredential
$RS.Url = $reportServerURI

# Set up some variables to hold referenced results from Render
$deviceInfo = “<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>”
$extension = “”
$mimeType = “”
$encoding = “”
$warnings = $null
$streamIDs = $null

#Here’s where we use the $reportPath see above
$reportPath = “/iscoolReports/UICustomReport4dc3c6e9ca34440e9119fe3c2920f87a”
$Report = $RS.GetType().GetMethod(“LoadReport”).Invoke($RS, @($reportPath, $null))

# Report parameters are handled by creating an array of ParameterValue objects.
#We don’t need to specify additional parameters because we used the save to Management Pack procedure
$parameters = @()
$RS.SetExecutionParameters($parameters, “en-us”) > $null
$RenderOutput = $RS.Render(‘PDF’,
$deviceInfo,
[ref] $extension,
[ref] $mimeType,
[ref] $encoding,
[ref] $warnings,
[ref] $streamIDs
)
# Here we specify where we are going to put the pdf file
$Stream = New-Object System.IO.FileStream(“d:\SCE\Reports\MyCoolServerAvailabilty.pdf“), Create, Write
$Stream.Write($RenderOutput, 0, $RenderOutput.Length)
$Stream.Close()

#Now we will Mail the report
$file = “d:\SCE\Reports\MyCoolServerAvailabilty.pdf
$smtpServer = “mailhost.iscool.local”
$att = new-object Net.Mail.Attachment($file)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = “SCE@iscool.local”
$msg.To.Add(samuel@iscool.local)
$msg.Subject = “My Cool server Availability report”
$msg.Body = “This report contains the availability of my cool server”
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()

Now use the schedule tasks to schedule your script and you’ll receive a neat email with the pdf as attachment.
And that’s it. I definitely think this can be useful in many other cases

Resources I used:
http://stackoverflow.com/questions/4196523/render-ssrs-report-with-parameters-using-soap-in-powershelL
http://blogs.technet.com/b/stefan_stranger/archive/2010/05/16/rendering-sql-reporting-reports-with-powershell.aspx

I was pretty excited by this, hope you are too.
Samuel.

Advertisements

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