This blog post is tested on SQL Server 2014 and PowerShell V5.
PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).
The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.
One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.
In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.
We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.
Here is an example:
SSRS parameters in SSDT Preview
This particular report utilizes different variations of parameters. To review, here are the available data types for SSRS report parameters:
SSRS Parameter Data Types
In the example above, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.
To back up your database to Azure BLOB storage, first you need to set up the BLOB storage in your Azure account. You will also need to create a container first.
You can back up the database to Azure by using either PowerShell or T-SQL.
Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.
The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.
This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.
The most recent Windows Management Framework (WMF) 5.0 Preview, the November 2014 preview, provides a glimpse of many improvements you can expect when PowerShell V5 is released.
A couple of the most noticeable additions are OneGet and PowerShellGet. If you have experience in *nix, these two new features are very similar to apt-get or yum or RPM. It allows you to connect to web-based package repositories, and install them.
Microsoft Second Shot is back for 2015! If you’re planning to upgrade your Microsoft certifications (MCSE, MCSA, MCSD), you might want to take advantage of this insurance. You will be eligible for a free retake (if you don’t pass the first time) if you take your exam between January 5, 2015 and May 31, 2015.
Check all the details here:
By the way, Microsoft has switched to Pearson as a testing provider. You may find that testing centres could be scarce in some locations at first, hopefully that picks up soon!
I contributed a chapter in Manning’s PowerShell Deep Dives book. All proceeds for this book go to Save the Children foundation.
Here is a quote of the description of the book from the Manning website:
PowerShell Deep Dives is a trove of essential techniques and practical guidance. It is rich with insights from experts who won them through years of experience. The book’s 28 chapters, grouped in four parts (Administration, Scripting, Development, and Platforms), were hand-picked by four section editors: Jeffery Hicks, Richard Siddaway, OisÃn Grehan, and Aleksandar Nikolić.
Whether you’re just getting started with PowerShell or you already use it daily, you’ll find yourself returning to this book over and over.
So 2012 has been a really hectic year for me so far. But… my book is out!
About This Book
- Provides over a hundred practical recipes that utilize PowerShell to automate, integrate and simplify SQL Server tasks
- Offers easy to follow, step-by-step guide to getting the most out of SQL Server and PowerShell
- Covers numerous guidelines, tips, and explanations on how and when to use PowerShell cmdlets, WMI, SMO, .NET classes or other components
- Builds a strong foundation that gets you comfortable using PowerShell with SQL Server–empowering you to create more complex scripts that you need in your day-to-day job
Who This Book Is For
This book is written for the SQL Server database professional (DBA, developer, BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks. A little bit of scripting background is helpful, but not necessary.
I’m trying to get some data I can play around with Tableau, fortunately information for Canadian climate data is available in CSV and XML from http://www.climate.weatheroffice.gc.ca/. I thought I’d go fetch the Vancouver daily weather data since 1953 using PowerShell.
Here’s a few PowerShell snippets for anyone interested. I haven’t cleaned these up yet, but should be pretty functional.
There are new rendering extensions supported in SSRS 2012. Welcome extensions are the OPENXML rendering formats, to be used with Word 2007/2010 (.docx) and Excel 2007/2010 (.xlsx).
To list the rendering extensions, you can use the ReportViewer from the Reporting Services Redistributable Package.
You can download from here
[System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, Version=126.96.36.199, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | Out-Null
$ReportServer = "http://KERRIGAN:80/ReportServer"
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer
$rv.ProcessingMode = "Remote"
$rv.ServerReport.ReportServerUrl = $ReportServer
This should list all the currently supported extensions.