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