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

Advertisements

Create SSRS Data Source

Continuing on with my earlier discussion on SSRS and testing data sources.  I’ve come up with a means to create a data source.  This article will demonstrate how I did that.

In testing the data source I had to create a proxy to the SSRS server again we’ll need to do the same thing so we can get to the Create method for the Data source.

$reportWebService = 'http://yourReportServer/ReportServer/ReportService2010.asmx'
$credentials = Get-Credential
$reportproxy = New-WebServiceProxy -uri $reportWebService -Credential $credentials

The reportWebService is a link to my Webservice on my ssrs instance which when proxied will allow me to get at all the methods and properties of this class Reportservice2010.asmx

The method we’ll be using for this discussion is ReportingService2010.CreateDataSource.

This method requires three variables.

[string] DataSource,
[string] Parent,
[boolean]Overwrite,
[DataSourceDefinition] Definition,
[Property[] ]Properties

The Datasource is a String = The name for the data source including the file name and, in SharePoint mode, the extension (.rsds).

Parent = The fully qualified URL for the parent folder that will contain the data source.  In My case I’m going to use /ThomTest

Where the location from root on the SSRS server is the folder Named ThomTest.

Overwrite = This tells the function if it finds it to overwrite what is there.

DataSourceDefition = This is a DataSourceDefinition class that contains the values for the DataSource. This includes things like:

ConnectStringCredentialRetrieval, Enabled, EnabledSpecified ImpersonateUserImpersonateUserSpecifiedPasswordPrompt, UserName, WindowsCredentials

For each of the above properties here is what I’ve been able to discover so far for where they are used:

2016-08-03 16_08_38-Clipboard

[Property[] ]Properties =  ReportService2010.Property[]– an array of properties that are nearly the same thing as the data source definition. So some of the same data collected to create the data source definition is used in this property Array collection.

The tough part of this creation of the datasource was getting the values passed into the PowerShell function to be accepted by the proxied method.  In order to do this I stumbled on this great article on StackOverflow. This allowed me to get at the classes from the proxied webservice via calls similar to the one below:

$ssrsproxy = New-SSRSProxy -reportWebService $reportWebService `
-Credentials $credentials
 $proxyNameSpace = $ssrsproxy.gettype().Namespace

So in order to get to the class I need for the DataSourceDefinition .  All i need to do is take the ProxyName space and append it to the proxied name space.

$proxyNameSpace = $ssrsproxy.gettype().Namespace 
$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition")

Now my $datasourceDef is a DatasourceDefinition object which contains the properties I showed above.  Since it is now in an object all I need to do now to set the items I need is to refer to them via . notation:

$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition")
PS PS:\> $datasourceDef


Extension : 
ConnectString : 
UseOriginalConnectString : False
OriginalConnectStringExpressionBased : False
CredentialRetrieval : Prompt
WindowsCredentials : False
ImpersonateUser : False
ImpersonateUserSpecified : False
Prompt : 
UserName : 
Password : 
Enabled : False
EnabledSpecified : False

PS PS:\> $datasourcedef.Connectstring = 'MyConnectionSTring'

Ok now the fourth parameter is the tough one this is where I had to get help from @Poshoholic on how to get a hashtable for the values into a Array of properties that the create will accept.

Here is what the Hashtable looks like:

 PS PS:\> $datasourceDefHash = @{
 'ConnectString' = $connectString; 'UserName' = $username; `
'Password' = $password; 'WindowsCredentials' = $windowsCredentials; `
'Enabled' = $enabled; 'Extension' = $Extension; `
'ImpersonateUser' = $ImpersonateUser; 'ImpersonateUserSpecified' = $true; `
'CredentialRetrieval' = $credentialRetrieval
 }

My understanding of what is needed is a property Collection so I named my variable a property collection:

 $propertyCollection = $datasourceDefHash.Keys.foreach`
{ @{ Name = $_; Value = $dataSourceDefHash[$_] }`
 -as "${proxyNamespace}.property" }

The magic here is where we are iterating through our keys and then casting each name and value to the $proxynamespace.property which is our ReportService2010.Property[] array.  @Poshoholic informed that because the name of the class is dynamic we have to use the -as key word to allow it to be ‘cast’ into the property we need.  Wow I’m glad he helped me or I’d have been here a very long time.

Now to put it all together. I originally wrote this function to all for continuous deployments and creation of data sources. The only value I really wanted to use was the Storing of the username and password (Credentials stored securely in the report server). In addition I need the checkbox for this option checked ( Use as Windows credentials when connecting to the data source).  with the Username and password entered upon calling the function.

So here is what my param block looks like:

  param
 (
 [Parameter(Mandatory = $false)]
 [string]$DataSourceName,
 [string]$path,
 [Parameter(Mandatory = $false)]
 [uri]$reportWebService,
 [string]$connectString,
 [string]$password,
 [string]$username,
 [ValidateSet('SQL','SQLAZURE','OLEDB','OLEDB-MD','ORACLE','ODBC','XML',`
'SHAREPOINTLIST','SAPBW','ESSBASE','Report Server FileShare','NULL'`
,'WORDOPENXML','WORD','IMAGE','RPL','EXCELOPENXML','EXCEL','MHTML',`
'HTML4.0','RGDI','PDF','ATOM','CSV','NULL','XML')]
 [string]$Extension = 'SQL',
 [boolean]$windowsCredentials = $false,
 [boolean]$enabled = $true,
 [boolean]$ImpersonateUser = $false ,
 [ValidateSet('None', 'Prompt', 'Integrated', 'Store')]
 [string]$credentialRetrieval = 'Store',
 [System.Management.Automation.PSCredential]$credentials
 )

Now that I have my user passing in their credentials and the items I need with the default values I can now call some of the methods and Items I described above:

  #https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
 $ssrsproxy = New-SSRSProxy -reportWebService $reportWebService -Credentials $credentials
 $proxyNameSpace = $ssrsproxy.gettype().Namespace
 #https://msdn.microsoft.com/en-us/library/reportservice2010.datasourcedefinition.aspx
 $datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition") #definition is needed because the create expects and object with some of the properties set.
 #$dataSourceProps = New-Object ("$proxyNameSpace.property")
 #$ssrsExtensions = ($ssrsproxy.ListExtensions('All')).name `
 #-join "','" for creating the set statement for extensions.
 #for some reason you have to set the extension and datasouce `
 in the definition before attempting to create. 
 $datasourceDef.connectstring = $connectString
 $datasourcedef.Extension = $Extension
 if ($credentialRetrieval -eq 'Store')
 {
 $datasourceDef.WindowsCredentials = $WindowsCredentials
 $datasourceDef.password = $password
 $datasourceDef.CredentialRetrieval = $credentialRetrieval
 $datasourceDef.username = $username
 }
 $datasourceDefHash = @{
 'ConnectString' = $connectString; 'UserName' = $username; 'Password' = $password; 'WindowsCredentials' = $windowsCredentials; 'Enabled' = $enabled; 'Extension' = $Extension; 'ImpersonateUser' = $ImpersonateUser; 'ImpersonateUserSpecified' = $true; 'CredentialRetrieval' = $credentialRetrieval
 }
 #convert the hashtable to an array of proxynamespace property items. https://msdn.microsoft.com/en-us/library/reportservice2010.property.aspx
 $propertyCollection = $datasourceDefHash.Keys.foreach`
{ @{ Name = $_; Value = $dataSourceDefHash[$_] } -as "${proxyNamespace}.property" }
 try
 {
 $ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, $propertyCollection)
 }
 catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }

The actual piece that is doing the creation of the data source is this line

$ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, `
$propertyCollection)

The script in its full form is below:

<#
 .SYNOPSIS
 Creates an SSRS data source
 
 .DESCRIPTION
 This script creates a datasource from the PowerShell prompt.
 
 .PARAMETER DataSourceName
 A description of the DataSourceName parameter.
 
 .PARAMETER path
 Path to where the datasource will be created. This should be the root of where the source is created.
 /report/report data source will be created at the second report value.
 
 .PARAMETER reportWebService
 URI to the location of the reportingService 2010 asmx page.
 
 .PARAMETER connectString
 This is the connection string that you use to connect to your database.
 
 .PARAMETER password
 Password to use if you are storing the credentials on the SQL server.
 
 .PARAMETER UserName
 Username to use for the connection if you are storing the credentiasl on the SQL Server.
 
 .PARAMETER Extension
 The Extension parameter is described as the Data Source Type in the new data source window in SSRS. Depending on your installation you may or may not have the items specified in the set statement for this function:
 'SQL' = SQL Server Connection
 'SQLAZURE' = SQL Azure Connection
 'OLEDB' = OLEDB connection 
 other possible connections include: 'OLEDB-MD','ORACLE','ODBC','XML','SHAREPOINTLIST','SAPBW','ESSBASE','Report Server FileShare','NULL','WORDOPENXML','WORD','IMAGE','RPL','EXCELOPENXML','EXCEL','MHTML','HTML4.0','RGDI','PDF','ATOM','CSV','NULL','XML'
 
 .PARAMETER windowsCredentials
 windowsCredentials = When using 'Store' with credential retrieval this sets the data source to 'Use as Windows credentials when connecting to the data source' 
 
 .PARAMETER enabled
 This Tells SSRS to enable the data source.
 
 .PARAMETER ImpersonateUser
 SEt this to true if you want to use the 'Impersonate the authenticated user after a connection has been made to the data source'.
 
 .PARAMETER credentialRetrieval
 CredentialRetrieval = one of four values:
 None = Credentials are not required
 Store = Credentials stored securely in the report server
 requires setting the username and password and optional params are impersonate and windowsCredentials
 Prompt = Credentials supplied by the user running the report
 Integrated = Windows integrated security
 
 .PARAMETER Credentials
 The credentials parameter is required to access the web service. They should be [System.Management.Automation.PSCredential] type
 
 .PARAMETER WebService
 This is the url to the Webservice which allows for creation of 
 
 .EXAMPLE
 PS C:\> $reportWebService = 'http://mySSRSServer//reportserver/reportservice2010.asmx'
 PS C:\> New-SSRSDataSource -DataSourceName 'ThomTest' -path '/ThomTest' -reportWebService $ReportWebService -connectString 'Data Source=servername;Initial Catalog=DB;Integrated Security=True' -username 'domain\user' -password 'password' -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -impersonateuser $true -credentials $credentials
 
 .NOTES
 Additional information about the function.
#>
function New-SSRSDataSource
{
 [CmdletBinding()]
 param
 (
 [Parameter(Mandatory = $false)]
 [string]$DataSourceName,
 [string]$path,
 [Parameter(Mandatory = $false)]
 [uri]$reportWebService,
 [string]$connectString,
 [string]$password,
 [string]$username,
 [ValidateSet('SQL','SQLAZURE','OLEDB','OLEDB-MD','ORACLE','ODBC','XML','SHAREPOINTLIST','SAPBW','ESSBASE','Report Server FileShare','NULL','WORDOPENXML','WORD','IMAGE','RPL','EXCELOPENXML','EXCEL','MHTML','HTML4.0','RGDI','PDF','ATOM','CSV','NULL','XML')]
 [string]$Extension = 'SQL',
 [boolean]$windowsCredentials = $false,
 [boolean]$enabled = $true,
 [boolean]$ImpersonateUser = $false ,
 [ValidateSet('None', 'Prompt', 'Integrated', 'Store')]
 [string]$credentialRetrieval = 'Store',
 [System.Management.Automation.PSCredential]$credentials
 )
 #https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
 $ssrsproxy = New-SSRSProxy -reportWebService $reportWebService -Credentials $credentials
 $proxyNameSpace = $ssrsproxy.gettype().Namespace
 #https://msdn.microsoft.com/en-us/library/reportservice2010.datasourcedefinition.aspx
 $datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition") #definition is needed because the create expects and object with some of the properties set.
 #$dataSourceProps = New-Object ("$proxyNameSpace.property")
 #$ssrsExtensions = ($ssrsproxy.ListExtensions('All')).name #-join "','" for creating the set statement for extensions.
 #for some reason you have to set the extension and datasouce in the definition before attempting to create. 
 $datasourceDef.connectstring = $connectString
 $datasourcedef.Extension = $Extension
 if ($credentialRetrieval -eq 'Store')
 {
 $datasourceDef.WindowsCredentials = $WindowsCredentials
 $datasourceDef.password = $password
 $datasourceDef.CredentialRetrieval = $credentialRetrieval
 $datasourceDef.username = $username
 }
 $datasourceDefHash = @{
 'ConnectString' = $connectString; 'UserName' = $username; 'Password' = $password; 'WindowsCredentials' = $windowsCredentials; 'Enabled' = $enabled; 'Extension' = $Extension; 'ImpersonateUser' = $ImpersonateUser; 'ImpersonateUserSpecified' = $true; 'CredentialRetrieval' = $credentialRetrieval
 }
 #convert the hashtable to an array of proxynamespace property items. https://msdn.microsoft.com/en-us/library/reportservice2010.property.aspx
 $propertyCollection = $datasourceDefHash.Keys.foreach{ @{ Name = $_; Value = $dataSourceDefHash[$_] } -as "${proxyNamespace}.property" }
 try
 {
 $ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, $propertyCollection)
 }
 catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }

}

function New-SSRSProxy
{
 param
 (
 [string]$reportWebService,
 [Parameter(Mandatory = $true,
 ValueFromPipeline = $true,
 ValueFromPipelineByPropertyName = $true)]
 [System.Management.Automation.PSCredential]$Credentials
 )
 Begin
 {
 if ($reportWebService -notmatch 'asmx')
 {
 $reportWebService = "$reportWebService/ReportService2010.asmx?WSDL"
 #$reportWebServiceurl = $reportWebServiceUrl.Replace("//","/")
 }
 }
 Process
 {
 #Create Proxy
 Write-Verbose "Creating Proxy, connecting to : $reportWebService"
 $ssrsProxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -ErrorAction 0
 #Test that we're connected
 $members = $ssrsProxy | get-member -ErrorAction 0
 if (!($members))
 {
 if (!$Credentials)
 {
 $Credentials = Get-Credential -Message 'Enter credentials for the SSRS web service'
 }
 Else
 {
 }
 $ssrsProxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials
 }
 $ssrsProxy
 }
 End { }
}

I hope this helps someone

Until then keep scripting

 

Thom

Testing an SSRS DataSource

Recently I’ve been working with SSRS and adding removing Datasources and Testing them. This article describes how I got there:

The first thing I did was to figure out how I was going to connect to the SSRS website.  I found that the best way to do this was with a new-webserviceproxy. This proxy requires a uri and a credential object:

$reportWebService = 'http://yourReportServer/ReportServer/ReportService2010.asmx'
$credentials = Get-Credential
$reportproxy = New-WebServiceProxy -uri $reportWebService -Credential $credentials

The reportWebService is a link to my Webservice on my ssrs instance which when proxied will allow me to get at all the methods and properties of this class Reportservice2010.asmx

Now the next thing I had to figure out is if the value that I’ll pass for my $datasource is actually a Data source.  So after sleuthing around I found the method to be able to determine the type that the passed in text is for my datasource.  The name of the method is getitemType if I pass the value for my $datasource to this method it’ll return me what type of “thing” it is. So using the report proxy above I’m going to see what type of return I’ll get.

$datasource = '/ssrs/mydatasource'
$reportproxy.GetItemType($datasource)   
DataSource

To find the different types that are possible running the Listitemtypes() against the report proxy will give you the different types that are possible.

PS C:\> $reportproxy.listitemtypes() 
Unknown 
Folder 
Report 
Resource 
LinkedReport 
DataSource 
Model 
Site 
DataSet 
Component

AS you can see there are several different item types.  The one we want to test with is the DataSource.  Now that we know that the use passed us a Data source we can now see if we have the ability to view it.  This is done with the GetDataSourceContents method.

$validObject = $reportProxy.Getdatasourcecontents($datasource)
Extension : SQL 
ConnectString : Data Source=SomeServer;Initial Catalog=dsn1 
UseOriginalConnectString : False 
OriginalConnectStringExpressionBased : False 
CredentialRetrieval : Store 
WindowsCredentials : True 
ImpersonateUser : False 
ImpersonateUserSpecified : True 
Prompt : Type or enter a user name and password to access the data source: 
UserName : yourDomain\ServiceAccount
Password : 
Enabled : True 
EnabledSpecified : True

So now that we know we can get to the Data source now we can go through testing it. This is done by calling the following method TestConnectForItemDataSource To do this the method requires 4 parameters and this is where i spent a great deal of time trying to figure out what the right params to pass were.  Three of them are in the $Validobject variable.  The third one is a [ref] type that I had not used before.  So here is how I was able to do this:

$tempRef = $true # have to initialize a variable so it can be used as a reference
# in the next method call
 $validConnect = $reportproxy.TestConnectForItemDataSource `
($datasource, $datasource, ($validObject.username), `
($validObject.password), ([ref]$tempRef))

The first variable $datasource is our datasource. The second is the datasource again.  This tripped me up because I was thinking i’d have to parse the datasource name out of the fully qualified datasource. I tried passing the name of the data source or the full path to the datasource and both seemed to work equally well so I took the less work method datasource,datasource.

The third param is the user name. Fourth is the password which when you look at the variable in shell it appears $null. If you try to get the contents nearly every query comes back with errors on a null valued expression.  Well I carried on here and assumed that the server had the password because  I can test in the GUI and it works fine.

Now onto the fifth param. I first passed a blank variable here and I would get errors:

Argument: ‘4’ should be a System.Management.Automation.PSReference. Use [ref].

I then ran this same function with [ref]$temp and I found that it would error because it didn’t exist yet thanx to Joel Bennet I was able to figure it out

At line:1 char:1
+ $reportproxy.TestConnectForItemDataSource($datasource, $datasource, ( …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (tempRef:VariablePath) [], RuntimeException
+ FullyQualifiedErrorId : NonExistingVariableReference

So I declared the variable and then I was able to succesfully test the connection:

$validConnect = $reportproxy.TestConnectForItemDataSource`
($datasource, $datasource, ($validObject.username), `
($validObject.password), ([ref]$tempRef))
 
True

Now that I have a return of true or false I can add the test value and datasource to my $validobject variable:

 $validObject | Add-Member -type NoteProperty -Name 'Valid' -Value $validConnect
 $validObject | Add-Member -Type NoteProperty -Name 'DataSource' -Value $datasource
 [pscustomobject]$validObject

Now that is done I can now show you the entire function with all the items added above + another function to do a litte house keeping on the Passed in data source.  I have more to this set of functions on a gist script in my repo called SQLReporting.ps1  https://gist.github.com/crshnbrn66/

 

<#
 .SYNOPSIS
 Test to see if the Datasource passed is valid
 
 .DESCRIPTION
 Checks to see if the data source exists. Then will test the data source to see if it is operational
 
 .PARAMETER datasource
 This is the full path to the datasource. where in the example datasource1 is our datasource name.
 for example: \myapp\mydatasource\data sources\datasource1
 example 2: \datasource1
 
 .PARAMETER reportWebService
 A description of the reportWebService parameter.
 
 .PARAMETER Credentials
 You must pass a pscredential object
 
 .PARAMETER NoTest
 if this is passed a test will not be performed on the data source.
 
 .NOTES
 Additional information about the function.
#>
function Test-ValidDataSource
{
 [CmdletBinding()]
 param
 (
 [Parameter(Mandatory = $true,
 ValueFromPipeline = $true,
 ValueFromPipelineByPropertyName = $true)]
 [string]$datasource,
 [Parameter(Mandatory = $true,
 ValueFromPipeline = $true,
 ValueFromPipelineByPropertyName = $true,
 HelpMessage = 'Provide the full path to the DataSource')]
 [uri]$reportWebService,
 [Parameter(Mandatory = $true,
 ValueFromPipeline = $true,
 ValueFromPipelineByPropertyName = $true,
 HelpMessage = 'You must pass a pscredential object')]
 [System.Management.Automation.PSCredential]$Credentials,
 [switch]$NoTest
 )
 
 $datasource = Normalize-SSRSFolder $datasource
 try
 {
 $reportProxy = new-webserviceproxy -uri $reportWebService -Credential $credentials
 $reportType = $reportProxy.getitemtype($datasource)
 Write-Debug $reportType
 if ($reportType -eq 'DataSource')
 {
 try
 {
 $validObject = $reportProxy.Getdatasourcecontents($datasource)
 if ($validObject.gettype().name -eq 'DataSourceDefinitionOrReference' -or 'DataSourceDefinition')
 {
 
 if ($NoTest)
 {
 $validConnect = $false
 }
 else
 {
 $tempRef = $true # have to initialize a variable so it can be used as a reference in the next method call
 $validConnect = $reportproxy.TestConnectForItemDataSource($datasource, $datasource, ($validObject.username), ($validObject.password), ([ref]$tempRef))
 }
 $validObject | Add-Member -type NoteProperty -Name 'Valid' -Value $validConnect
 $validObject | Add-Member -Type NoteProperty -Name 'DataSource' -Value $datasource
 [pscustomobject]$validObject
 }
 else
 {
 $invalid = "invalidobject or permssion"
 [pscustomobject]@{
 'Extension' = $invalid
 'ConnectString' = $invalid
 'UseOriginalConnectString' = $false
 'OriginalConnectStringExpressionBased' = $false
 'CredentialRetrieval' = $invalid
 'ImpersonateUserSpecified' = $false
 'WindowsCredentials' = $false
 'Prompt' = $invalid
 'UserName' = $invalid
 'Password' = $invalid
 'Enabled' = $false
 'EnabledSpecified' = $false
 'Valid' = $false
 }
 }
 }
 catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }
 
 
 }
 }
 catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }
}

function Normalize-SSRSFolder
{
 param
 (
 [string]$Folder
 )
 
 if (-not $Folder.StartsWith('/'))
 {
 $Folder = '/' + $Folder
 }
 elseif ($Folder -match '//')
 {
 $Folder = $Folder.replace('//','/')
 }
 
 return $Folder
}

 

I hope this helps someone

Until then keep scripting

 

Thom

Sharepoint RootFolder in Query String

csm_sp2013logo_0abaeddb68                     reportingservices

I’ve been working on a GUI program in Sapien Powershell Studio.  This program deploys SSRS reports to a integrated SSRS and Sharepoint instance.  While writing this I discovered that to upload the Files to sharepoint you must have the full path structure to where you are placing the file.  Well turns out what sharepoint gives you when you are browsing to a subfolder is this very weird url with query strings in it. To ensure that I get what I want from my user and I know where to upload the file I had to write a function to parse the query string and return it to my calling function so I could upload properly. This article is about how i was able to get this to function.

First lets look at  the query string I was dealing with:

http://servername/sites/mysite/Shared%20Documents/Forms/Allitems.aspx?RootFolder=%2fsites%2fmysite%2fTest&folderCTID=0x0120004845B289B9EC1E479DF75ADD1F150A9E

So if we look at this query string closely we can see the real folder name for my Sharepoint site and location is after the RootFolder query string %2fsites%2fmysite%2fTest 

So with that in mind I started looking for a way to parse this string and get out what I wanted after the RootFolder query string.  So I attempted using string manipulation and several other methods.  I kept thinking to myself there must be a better way.  So I dug deeper and I found a #Csharp Class that deals with urls: [System.Web.HttpUtility]

Now that I have something to deal with urls.  I sleuth around the class with Powershell until I found this function ParseQueryString.  Now if I take my url and feed it to this class. To be able to use it I must add the type so that Powershell Can see it:

 Add-Type -AssemblyName System.Web
 PS PS:\> [uri]$Url = 'http://servername/sites/mysite/Shared%20Documents/Forms/Allitems.aspx?RootFolder=%2fsites%2fmysite%2fTest&folderCTID=0x0120004845B289B9EC1E479DF75ADD1F150A9E'

[System.Web.HttpUtility]::ParseQueryString($url) http://servername/sites/mysite/Shared Documents/Forms/Allitems.aspx?RootFolder

My return results don’t seem to be what I’m looking for. Since my return results contain two items which look to be an array.  I’ll query them via index number:

PS PS:\> $a = ([System.Web.HttpUtility]::ParseQueryString($url))
PS PS:\> $a[0]
/sites/mysite/Test

Interesting The results are in the first array member so this must be a hashtable of some sort?

PS PS:\> $a.gettype()

IsPublic IsSerial Name BaseType
——– ——– —- ——–
False True HttpValueCollection System.Collections.Specialized.NameValueCollection

No it’s not a Hashtable but a specialized System.Collections.Specialized.NameValueCollection. Hmmm so how do i get the value I want out of the collection?

PS PS:\> $a | get-member -MemberType Properties

TypeName: System.String

Name MemberType Definition
—- ———- ———-
Length Property int Length {get;}

The only property it has is Length which doesn’t really give me what I want the actual text from the query string.  So it must be a method that I need to use:

PS PS:\> $a | get-member -MemberType Method
TypeName: System.String

Name MemberType Definition
—- ———- ———-
Clone Method System.Object Clone(), System.Object ICloneable.Clone()
CompareTo Method int CompareTo(System.Object value), int CompareTo(string strB), int IComparable.CompareTo(System.Object obj), int IComparable[string].CompareTo(string other)
Contains Method bool Contains(string value)

Hmm this variable now says its of type string and all the methods are string methods. After looking around for some time I finally tried somethings in ISE and I got to the method I was looking for: GetValues

system.collections.specilaized.namevaluecollection.getValues

So now I can attempt to see if I can get the values I want from this object:

PS PS:\> $a.GetValues('http://servername/sites/mysite/Shared Documents/Forms/Allitems.aspx?RootFolder')

Cool I got what i was looking for the value for RootFolder query string. Now I’m half way there. Now all that is needed is to construct the getValues method call to only get what I’m looking for RootFolder. 

PS PS:\> $a.GetValues(($a.keys | Where-Object{$_ -like "*RootFolder*"}))</span>

Now I have the value I want so I can now create a function to return the url I need for my program.  Here is the full Function:

function Format-SharepointUrl
{
 param
 (
 [parameter(Mandatory = $true)]
 [uri]$url
 )
add-type -assembly system.web
 $newUrl = $null
 [System.Collections.Specialized.NameValueCollection]$uCollection = [System.Web.Httputility]::ParseQuerySTring($url)
 if ($uCollection.GetValues(($ucollection.keys | ?{ $_ -like "*RootFolder*" })))
 {
 $cUrl = $uCollection.GetValues(($ucollection.keys | ?{ $_ -like "*RootFolder*" }))
 $newUrl = "$($url.Scheme)://$($url.Host)$cUrl"
 }
 $newUrl
}

I hope this helps someone ..

Until then keep scripting