SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

February 2013 - Posts

A year of SQL Lunchs

Time does fly, and happy birthday SQL Lunch UK.


It has been a year since the first SQLLunch UK, and over the year there have been 21 Lunchs with a total of 537 attendees.  OK, some of those will be the same people but in ‘conference speak’ each registration counts, but in any case i’m delighted by the success and looking forward to repeating this over the coming year.  The first few presented some interesting technical challenges, those have been licked and now with the scaling out to Cardiff we have a few more but nothing that is insurmountable.

To ‘celebrate’ this occasion it is only fitting that Patrick LeBlanc (b|t) will be presenting a session for us on the 26th of Feb.  Full details will be on SQL Server FAQ soon,  so hope to see you in either London or Cardiff.

A massive thank-you to the Attendees for coming along twice a month for a pint and SQL knowledge and an even bigger one to all of the speakers many of who have stayed up late (12:30am in New Zealand )  or up early (4:30am in Seattle) ,  your efforts are very much appreciated.

Deploying SSRS artefacts using Powershell ( Simply)

As ive stated in the past,  i’m presently part of a team on an ALM project for a major UK based online retailer.  The aim of this project is to have fully automated app and database code and schema deployment, through development, UAT and finally live.

Most parts of this equation we have pretty much got licked, SSDT is used to generate diff scripts, a custom powershell script copies SSIS packages and install relevant sql agent jobs. One major missing part though was SSRS deployments. 

Bingling around there are a few options,  RS.EXE is a vbscript language for the job, custom MSBUILD tasks, or RSS scripter.  None of these felt quite right to me, or as intuitive and extensible as I personally like things to be.

In a previous life I have written a few jobs for emailing SSRS reports directly, so was quite aware that SSRS does has a feature rich WSDL layer underneath it.  However,  since at that time my language of choice was C/C++ the WSDL was a PITA to work with and didn't really enjoy the experience.  Now using powershell though they work like a dream and can easily be exploited.

Apologies in advance for the overuse of the word “simple” from here on, but it really is simple.  I will call out the important parameters but wont spend a lot of time describing each one.

So, to the details.  The WSDL we will be using is ReportService2010,  this can be found at the url …


naturally replacing YOURHOST and YOURINSTANCE with your host server and instance

The class ReportingService2010 is documented here.

Connecting to it and creating the service in powershell is simple:

$ReportServerUri = "http://server/ReportServer_INSTANCE//ReportService2010.asmx?wsdl"
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ;

Notice that extra credentials can be supplied here,  to keep things simple im going to use default. 

The first thing that we would like to do would be to create a menu structure:

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Property')
$property =New-Object ($datatype);
$property.Name = “NewFolder”
$property.Value = “NewFolder”
$numproperties = 1
$properties = New-Object ($datatype + '[]')$numproperties 
$properties[0] = $property;
$newFolder = $proxy.CreateFolder(“NewFolder”, “/”, $properties);

Things to note in this code : The name of the object type created in the New-Object command is the full namespace of the wsdl. The CreateFolder function creates a folder called NewFolder in the root directory.  Check the full documentation for other properties that can be added.

Finding the children of a menu node is equally simple:

$Children =$proxy.ListChildren("/",$false)

Nice, our menu structure is now defined, lets do something useful:  Lets add an RDL file, here comes the tricky part. No not really, its simple

$stream = Get-Content 'MyAwesomeReport.rdl' -Encoding byte
$warnings =@();
$proxy.CreateCatalogItem("Report","My Awesome Report","/",$true,$stream,$null,[ref]$warnings)

Here “My Awesome Report” is the name of the report and “/” is the path to create it.

Creating a datasource is equally as simple, we have a few more bits of data to set up but nothing to taxing:

$Name = "MyDSN"
$Parent = "/"
$ConnectString = "data source=(local);initial catalog=AdventureWorks"
$type = $Proxy.GetType().Namespace
$DSDdatatype = ($type + '.DataSourceDefinition')
$DSD = new-object ($DSDdatatype)
if($DSD -eq $null){
      Write-Error Failed to create data source definition object
$CredentialDataType = ($type + '.CredentialRetrievalEnum')
$Cred = new-object ($CredentialDataType)
#     $CredEnum = ($CredentialDataType).Integrated
$Cred.value__=2    ##   .Integrated
$DSD.CredentialRetrieval =$Cred
$DSD.ConnectString = $ConnectString
$DSD.Enabled = $true
$DSD.EnabledSpecified = $false
$DSD.Extension = "SQL"
$DSD.ImpersonateUserSpecified = $false
$DSD.Prompt = $null
$DSD.WindowsCredentials = $false
$newDSD = $proxy.CreateDataSource($Name,$Parent,$true,$DSD,$null)

This is one part I havent worked out yet though, the securitytype is a enum and I cant find the correct way to use that.  Anyone care to help me here ?, i’m no powershell guru.

So, by now we can build a menu structure, add datasources and reports all with a few simple lines of powershell code.

But, in the real world, we dont just want anyone to be able to run any report, so lets add some security on.  This is simple :

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Policy')

$NewPolicies = @();
$NewPolicy = New-Object ($datatype);
$NewPolicy.GroupUserName = "MYDOMAIN\MYUSER";

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Role')
$NewRole = New-Object ($datatype);
$NewRole.Name = "Content Manager"

$NewPolicies += $NewPolicy
$datatype = ($type + '.Policy')
$NewPolicy = New-Object ($datatype);
$NewPolicy.GroupUserName = "MYDOMAIN\MYGroup";

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Role')
$NewRole = New-Object ($datatype);
$NewRole.Name = "Browser"
$NewPolicies += $NewPolicy

$proxy.SetPolicies("/My Report",$NewPolicies)

Hope that you have found this post useful, and now you can proceed with automated deployments of SSRS projects.  To my mind this post covers 90% of the work that you will need to do and you now have a good lead on the remainder, Oh, and did I mention, its simple.

This post has be part of #TSQL2sDay, hosted by Wayne Sheffield (b|t)