Creating a DataDriven Subscription from a file

In working with SSRS I found that if I wanted to do a delete of an RDL.  Problem is if I delete an RDL the Subscriptions are deleted as well.   So since I’ve put most all of my SSRS File management and Data source creation in Continuous Integration I needed a way to save off subscriptions before I attempted a delete.  I talk about how that is done in this post Saving SSRS Subscriptions to File.  This post will be about how I consume the saved off files and put the subscription in place in another environment.

If you’ve been following my other blog posts on SSRS you’ll know I’ve written about creating an SSRS Datasource. Testing an SSRS Datasource in each of these scripts I start with the following :  Reportservice2010.asmx Webservice to get to any functions that are needed to operate on SSRS. I assume you’ve read one of these articles and that you need to get a proxy to this.

Onto the method that we’ll call to recreate the DataDriven Subscription:

CreateDataDrivenSubscription A call to this method requires the following classes passed to it:

Path to where the report is : item

ExtensionSetttings – An ExtensionSettings object that contains a list of settings that are specific to the delivery extension.

DataRetrievalPlan – Type: ReportService2010.DataRetrievalPlan
A DataRetrievalPlan object that provides settings that are required to retrieve data from a delivery query. The DataRetrievalPlan object contains a reference to a DataSetDefinition object and a DataSourceDefinitionOrReference object.

description – Type: System.String A meaningful description that is displayed to users.

eventtype – Type: System.String
The type of event that triggers the data-driven subscription. The valid values are TimedSubscription or SnapshotUpdate

Matchdata – Type: System.String
The data that is associated with the specified EventType parameter. This parameter is used by an event to match the data-driven subscription with an event that has fired.

paramatervalueorfieldreference – Type: ReportService2010.ParameterValueOrFieldReference[]
An array of ParameterValueOrFieldReference objects that contains a list of parameters for the item.

Since I’m using a Powershell Class one of the first things I must make sure I do is have a proxy to the WebService I want to call. Everything else will fail if i haven’t done that first. Then I’ll read in my reportFiles that I want to operate on With Get-childitem.  I chose to use the xml export method.


$ssrsproxy = New-WebServiceProxy -Uri http://yourwebsite/yourreports/_vti_bin/ReportServer/ReportService2010.asmx -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'
$reportExportPath = 'C:\temp\reports3'
$reportFiles = Get-ChildItem $reportExportPath -Filter *.xml

Now that I have My reports that I want to operate on I can iterrate through each of these objects and rebuild the object and submit it to the new server.   So that I can change url’s from one server to another I Use a $source and $destination variable to assist.

Since each saved off report has 7 items in it we have to go through each item (object) in the xml and convert them back to the same type of object that the method call expects. So the first Object that we need to rebuild that is a little more complex is the $extensionsettings.  When you look at the extension settings they can have two other objects inside the extension setting. One of them is a ParameterFieldReference and the other is a Parametervalue.  So we have to test each of the names of the object properties to see what the name is so we know whether to build a ParameterFieldReference or a Parametervalue.


foreach($parameterField in $reportobject.extensionSettings.ParameterValues)
{
if($parameterfield.psobject.Properties.name[0] -eq 'ParameterName') #rebuild the object into an extenstion setting this one contains a parameter field reference
{
$a = [SSRSProxy.ParameterFieldReference]::new()
Write-Verbose 'Create a object of type ParameterField reference.'
$a.FieldAlias = $parameterfield.fieldalias
$a.ParameterName = $parameterfield.ParameterName
}
elseif($parameterfield.psobject.Properties.name[0] -eq 'Name') #rebuild the object into an extension settings object this one contains a param value
{
$a = [SSRSProxy.ParameterValue]::New()
Write-Verbose 'Create a object of type ParameterValue reference.'
$a.Label = $parameterField.Label
$a.Name = $parameterField.Name
$a.Value = $parameterField.Value
}
$paramvalues += $a
}
$extensionSettings.ParameterValues = [ssrsproxy.parametervalueorfieldreference[]]$paramvalues

Once we’ve built the extensionsettings we now need to rebuild the DataRetrievalPlan . The data retreival plan includes the reference to the new location for the DataSource.  this is where we use the source and destination to our advantage. This is done by settting the item on the dataretrieval plan to the datasource reference we wish to use using the object DataSourceReference


[SSRSProxy.DataRetrievalPlan]$DataRetrievalPlan = New-Object SSRSProxy.DataRetrievalPlan
 Write-Verbose 'Create a object of type DataRetrievalPlan reference.'

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet
 [SSRSProxy.DataSourceReference]$dsReference = $reportobject.DataRetrievalPlan.Item
 $src = ([uri]$source).absoluteuri
 $dest = ([uri]$destination).absoluteuri
 $dsReference.Reference = (([uri]$dsReference.Reference).AbsoluteUri) -replace $src,$dest
 Write-Verbose "Datasource Reference $dsreference use the value for the datasource you want this data driven report to consume"
 $DataRetrievalPlan.Item = $dsReference

now the last few bits of information that need to be added is the ParameterValueorFieldReference and the report description, eventtype and match data.


$description = $reportobject.Description
 $eventtype = $reportobject.eventtype
 $matchdata = $reportobject.matchdata

$b = [Ssrsproxy.parameterfieldreference]::new()
 Write-Verbose 'Create a object of type parameterfieldreference reference.'
 $b.FieldAlias = $reportobject.parameters.fieldalias
 $b.ParameterName = $reportobject.parameters.ParameterName
 [SSRSProxy.ParameterValueOrFieldReference]$ParameterValueOrFieldReference = $b

Now that we have those set the last thing for us to do is to set the destination for the report where this should go.  Then we’ll call the method and hope we don’t hit an exception.


$itemPath = "$destination/$($reportobject.subscription.report)"
 try
 {
 Write-Verbose "Now that the object is re-constituted we can put this in the SSRS instance we wish to push it to"
 $ssrsproxy.CreateDataDrivenSubscription($itempath , $extensionsettings , $DataRetrievalPlan, $description, $eventtype, $matchdata, $ParameterValueOrFieldReference) 
 }
 Catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }

 

Full script for this follows:

Write-Verbose " Destination for where the saved subscriptions will be pushed to"
$destination = 'http://yourwebsite/sites/datasourcetest/Shared%20Documents'
$source = 'http://yourwebsite/sites/Reports/Shared%20Documents'

$reportFiles = Get-ChildItem $reportExportPath -Filter *.xml
foreach($file in $reportFiles)
{
 $reportobject = Import-Clixml -path ($file.fullname)
 Write-Verbose "Create a object of type ExtensionSettings"
 $extensionSettings = New-Object -typename 'SSRSProxy.ExtensionSettings' 
 $extensionSettings.Extension = $reportobject.extensionSettings.Extension
 $paramvalues = @()
 foreach($parameterField in $reportobject.extensionSettings.ParameterValues)
 {
 if($parameterfield.psobject.Properties.name[0] -eq 'ParameterName') #rebuild the object into an extenstion setting this one contains a parameter field reference
 {
 $a = [SSRSProxy.ParameterFieldReference]::new()
 Write-Verbose 'Create a object of type ParameterField reference.'
 $a.FieldAlias = $parameterfield.fieldalias
 $a.ParameterName = $parameterfield.ParameterName
 }
 elseif($parameterfield.psobject.Properties.name[0] -eq 'Name') #rebuild the object into an extension settings object this one contains a param value
 {
 $a = [SSRSProxy.ParameterValue]::New()
 Write-Verbose 'Create a object of type ParameterValue reference.'
 $a.Label = $parameterField.Label
 $a.Name = $parameterField.Name
 $a.Value = $parameterField.Value
 }
 $paramvalues += $a
 }
 $extensionSettings.ParameterValues = [ssrsproxy.parametervalueorfieldreference[]]$paramvalues

 [SSRSProxy.DataRetrievalPlan]$DataRetrievalPlan = New-Object SSRSProxy.DataRetrievalPlan
 Write-Verbose 'Create a object of type DataRetrievalPlan reference.'

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet
 [SSRSProxy.DataSourceReference]$dsReference = $reportobject.DataRetrievalPlan.Item
 $src = ([uri]$source).absoluteuri
 $dest = ([uri]$destination).absoluteuri
 $dsReference.Reference = (([uri]$dsReference.Reference).AbsoluteUri) -replace $src,$dest
 Write-Verbose "Datasource Reference $dsreference use the value for the datasource you want this data driven report to consume"
 $DataRetrievalPlan.Item = $dsReference
 $description = $reportobject.Description
 $eventtype = $reportobject.eventtype
 $matchdata = $reportobject.matchdata

 $b = [Ssrsproxy.parameterfieldreference]::new()
 Write-Verbose 'Create a object of type parameterfieldreference reference.'
 $b.FieldAlias = $reportobject.parameters.fieldalias
 $b.ParameterName = $reportobject.parameters.ParameterName
 [SSRSProxy.ParameterValueOrFieldReference]$ParameterValueOrFieldReference = $b

 $itemPath = "$destination/$($reportobject.subscription.report)"
 try
 {
 Write-Verbose "Now that the object is re-constituted we can put this in the SSRS instance we wish to push it to"
 $ssrsproxy.CreateDataDrivenSubscription($itempath , $extensionsettings , $DataRetrievalPlan, $description, $eventtype, $matchdata, $ParameterValueOrFieldReference) 
 }
 Catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }
}

I hope this helps someone

Until then keep Scripting

Thom

Saving SSRS Subscriptions to File

In working with SSRS I found that if I wanted to do a delete of an RDL.  Problem is if I delete an RDL the Subscriptions are deleted as well.   So since I’ve put most all of my SSRS File management and Data source creation in Continuous Integration I needed a way to save off subscriptions before I attempted a delete.  This article is about the PowerShell I wrote to accomplish this task.

If you’ve been following my other blog posts on SSRS you’ll know I’ve written about creating an SSRS Datasource. Testing an SSRS Datasource in each of these scripts I start with the following :  Reportservice2010.asmx Webservice to get to any functions that are needed to operate on SSRS. I assume you’ve read one of these articles and that you need to get a proxy to this.

Onto the method that we’ll call to get the two different types of SSRS Subscriptions:

Normal subscriptions & DataDriven Subscriptions

For a normal subscription you’ll need to call the method listSubscriptions . This method expects the name of the report that you wish to get the subscriptions for.  To get the names of the reports we’ll use the .listChildren method and list all the children of the current site and then find each subscription for each report.

function Get-Subscriptions
{
  param([object]$ssrsproxy, [string]$site, [switch]$DataDriven)
  write-verbose 'Path to where the reports are must be specified to get the subscriptions you want.. Root (/) does not seem to get everything'
  $items = $ssrsproxy.ListChildren($site,$true) | Where-Object{$_.typename -eq 'report'}
  $subprops = $ddProps= @()

Now that we have the list of reports in the $items variable we can now ask for the subscription foreach item


foreach($item in $items)
 {
 $subs = $ssrsproxy.ListSubscriptions($item.path)

}

Now that we have our Subscriptions in the $subs variable we can now check to see if there was a return result if there was a return result we can then the the properties of each subscription type. We’ll know that we have a DataDriven subscripition by the property each of the subscriptions in the $subs array (.isdatadriven -eq $true)


 if($subs)
 {
   foreach($sub in $subs)
   {

    if($sub.isdatadriven -eq 'true')
      {
        $ddProps += Get-DataDrivenSubscriptionProperties -subscription $sub -ssrsproxy $ssrsproxy
      }
      elseif(-not $DataDriven)
      {
        $subProps += Get-SubscriptionProperties -subscriptionid $sub -ssrsproxy $ssrsproxy
      }

    }
 }
 if($DataDriven)
 {$ddProps}
 else {
 $subprops
 }

Now Onto explaining the Get-DataDrivenSubscriptionProperties and the Get-Subscription Properties.

The Get-SubscriptionProperties calls the method GetSubcriptionProperties with the ID of the subscription and then returns 7 objects through a reference.   In the function below I first set all the 7 reference variables to null then I call the method.  On successful return from the method I add to my powershell object [SSRSObject] (which is a  powershell class). If I choose to not use a class and instead want to use a standard object the standard object code is commented out so that it can be used if needed.


function Get-SubscriptionProperties
{
 param([string]$Subscription,
 [object]$ssrsproxy)
 $subextensionSettings = $subDataRetrievalPlan = $subDescription = $subactive = $substatus = $subeventtype = $submatchdata = $subparameters = $Null

 $subOwner = $ssrsproxy.GetSubscriptionProperties($subscription.SubscriptionID,[ref]$subextensionSettings,[ref]$subDescription,[ref]$subactive,[ref]$substatus,[ref]$subeventtype,[ref]$submatchdata,[ref]$subparameters)
 $ssrsobject = [SSRSObject]::New()
 $ssrsobject.subscription = $Subscription
 $ssrsobject.Owner = $subOwner
 $ssrsobject.ExtensionSettings = $subextensionSettings
 $ssrsobject.Description = $subDescription
 $ssrsobject.DataRetrievalPlan = $subDataRetrievalPlan
 $ssrsobject.Active = $subactive
 $ssrsobject.Status = $substatus
 $ssrsobject.EventType = $subeventtype
 $ssrsobject.MatchData = $submatchdata
 $ssrsobject.Parameters = $subparameters
 <#
 [PSCustomObject]@{
 'Owner' = $subOwner
 'extensionSettings' = $subextensionSettings
 'Description' = $subDescription
 'active' = $subactive
 'status' =$substatus
 'eventtype' =$subeventtype 
 'matchdata' = $submatchdata
 'parameters' = $subparameters
 }
 #>
}

For calling the Get-DataDrivenSubscriptionProperties we do all the same things as with the previous subscription type.  We call the method GetDataDrivenSubscriptionProperties it returns the same 7 reference objects.  On successful return from the method I add to my powershell object [SSRSObject] (which is a  powershell class). If I choose to not use a class and instead want to use a standard object the standard object code is commented out so that it can be used if needed.


function Get-DataDrivenSubscriptionProperties 
{
 param([object] $Subscription,
 [object]$ssrsproxy)
 $ssrsobject = [SSRSObject]::New()
 $sid = $Subscription.SubscriptionID
 $ddextensionSettings = $ddDataRetrievalPlan = $ddDescription = $ddactive = $ddstatus = $ddeventtype = $ddmatchdata = $ddparameters = $Null
 $ddOwner = $ssrsproxy.GetDataDrivenSubscriptionProperties($sid,[ref]$ddextensionSettings,[ref]$ddDataRetrievalPlan`
 ,[ref]$ddDescription,[ref]$ddactive,[ref]$ddstatus,[ref]$ddeventtype,[ref]$ddmatchdata,[ref]$ddparameters)

 $ssrsobject.subscription = $Subscription
 $ssrsobject.Owner = $ddOwner
 $ssrsobject.ExtensionSettings = $ddextensionSettings
 $ssrsobject.Description = $ddDescription
 $ssrsobject.DataRetrievalPlan = $ddDataRetrievalPlan
 $ssrsobject.Active = $ddactive
 $ssrsobject.Status = $ddstatus
 $ssrsobject.EventType = $ddeventtype
 $ssrsobject.MatchData = $ddmatchdata
 $ssrsobject.Parameters = $ddparameters
 $ssrsobject
 <# [PSCustomObject]@{
 'Owner' = $ddOwner
 'extensionSettings' = $ddextensionSettings
 'DataRetrievalPlan' = $ddDataRetrievalPlan
 'Description' = $ddDescription
 'active' = $ddactive
 'status' =$ddstatus
 'eventtype' =$ddeventtype 
 'matchdata' = $ddmatchdata
 'parameters' = $ddparameters
 } #>
}

Now that I have each of the reports in an object  I now persist this to disk with either Export-clixml or with convertto-json cmdlets

function New-XMLSubscriptionfile
{
[CmdletBinding()]
[Alias()]
param([psobject]$subscriptionObject, [string]$path)
if(test-path $path -PathType Leaf)
{
$path = split-path $path

}
if(-not(test-path $path))
{
mkdir $path
}
foreach($sub in $subscriptionObject)
{
$reportName = (($sub.subscription.report).split('.'))[0]
$filename = "$path\$reportName.xml"
$sub | Export-Clixml -Depth 100 -path $filename
}
}

function New-JsonSubscriptionFile
{
[CmdletBinding()]
[Alias()]
param([psobject]$subscriptionObject, [string]$path)
if(test-path $path -PathType Leaf)
{
$path = split-path $path

}
if(-not(test-path $path))
{
mkdir $path
}
foreach($sub in $subscriptionObject)
{
$reportName = (($sub.subscription.report).split('.'))[0]
$filename = "$path\$reportName.json"
$sub | convertto-json -Depth 100 | out-file $filename
}
}

To see the entire script see this Gist

I hope this helps someone

Until then keep Scripting

Thom

Deploying a Sharepoint App to Sharepoint Online

This article is about how I was able to use the SharePoint Modules to successfully deploy an application to SharePoint Online.

I’ve used some scripting before to update items in Sharepoint.  This blog article is how I took the build that IOZ tools creates and deploy it to Sharepoint Online.

First I needed to start with downloading the latest copy of SharePointPnPPowerShellOnline.

I discovered while using this module that there is the capability to add a PSDrive to my session.  This means I should be able to upload files to sharepoint as if it were a drive on my local machine.  Here is how you connect to sharpeoint online:

PS> Install-Module -Name SharePointPnPPowerShellOnline
$adminpassword = 'password'
$adminUserName = 'mysharepoint@onmicrosoft.com'
$creds = $AdminPassword | ConvertTo-SecureString -AsPlainText -Force
$SPdevcredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AdminUserName, $creds
connect-pnponline -Url $url -Credentials $SPdevcredentials -CreateDrive

Now that I have a connection to the SharePoint online instance I can see that I have a new powershell drive through the get-psdrive cmdlet:
get-psdrive

2017-01-10-07_33_05-clipboard

As you can see I have a new drive that is configured for use in my session, directorying the SPO: drive will get the contents of the SharePoint Site:

2017-01-10-07_49_31

My Applications are in my AppCatalog folder to get to that folder all I need to do is issue a CD to that directory.   To upload my App to this folder all I need to do is add it with Add-pnpFile.

2017-01-10 07_56_30.png

I seemed to have the best success when I used Get-Item (gi) and then used the fullname property for the file that I was sending to SharePoint. In addition one other gotcha was that the folder to upload to is a subfolder of the site you are connected to. In my case \sites\apps was my site I was connected to so specifying appcatalog was all I needed.

Now all that I needed to do was to put this in a script that  I could call from my CI automation and put some Error logic.  Now I have a full fledged script called deployspapp.ps1.   Full Source is found on my Gist:

I hope this helped someone

Until then keep scripting

Thom