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
3 thoughts on “Testing an SSRS DataSource”