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 …

http://YOURHOST/ReportServer_YOURINSTANCE//ReportService2010.asmx?wsdl

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"
$NewPolicy.Roles+=$NewRole
$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"
$NewPolicy.Roles+=$NewRole
$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)